第5章數(shù)據(jù)庫查詢與視圖

5.1SELECT語句的格式與簡單查詢5.2SELECT多表連接查詢與創(chuàng)建新表5.3用SELECT語句對數(shù)據(jù)進行統(tǒng)計匯總5.4SELECT合并結(jié)果集與子查詢5.5使用企業(yè)管理器查詢（編輯）數(shù)據(jù)5.6視圖的基本概念5.7視圖的創(chuàng)建與使用5.8查看、編輯和刪除視圖5.9實訓(xùn)要求與習(xí)題第5章數(shù)據(jù)庫查詢與視圖學(xué)習(xí)目的與要求創(chuàng)建數(shù)據(jù)庫的目的是為了有效的存儲管理數(shù)據(jù)，更重要的是對他們進行整理加工以獲得我們需要的重要信息。而查詢和視圖正是從數(shù)據(jù)庫中獲取信息最簡單、最常用、最主要的操作，是數(shù)據(jù)庫使用頻率最高的操作，是數(shù)據(jù)庫應(yīng)用的靈魂，其主要作用是根據(jù)用戶的請求，對眾多數(shù)據(jù)表的大量數(shù)據(jù)進行處理，篩選、查找、并統(tǒng)計出用戶需要的信息資料。通過本章學(xué)習(xí)，讀者應(yīng)理解和掌握SQLServer2000數(shù)據(jù)表的各種查詢操作，包括單表單條件查詢、單表多條件查詢、多表連接及多條件查詢、統(tǒng)計匯總查詢、子查詢及查詢結(jié)果的排序、分組；理解視圖的意義，掌握視圖的創(chuàng)建和使用。5.1SELECT語句的格式與簡單查詢5.1.1、SELECT查詢語句格式SELECT[記錄顯示范圍]字段列表[INTO新表名][FROM表名或表名列表及其連接方式][WHERE條件表達(dá)式][GROUPBY分組字段名列表[HAVING分組條件表達(dá)式]][ORDERBY排序字段名列表[ASC|DESC]][{COMPUTE集合函數(shù)(列名1)[BY列名2]}[…n]]說明：l

SELECT語句中各子句的順序：SELECT→[記錄范圍]→字段列表→[INTO]→[FROM]→[WHERE]→[GROUPBY→[HAVING]]→[ORDERBY→[COMPUTE]]l

FROM用于指定數(shù)據(jù)來源：單表查詢簡單格式：FROM表名多表查詢時的格式：FROM表名列表及其連接方式l

COMPUTE子句不能與INTO子句或GROUPBY子句同時使用。5.1.2、使用SELECT語句進行無數(shù)據(jù)源檢索

無數(shù)源檢索就是查詢輸出不在數(shù)據(jù)表中的數(shù)據(jù)。一般用來輸出常量或變量的值（相當(dāng)于PRINT或其他語言的輸出語句），也可用于查看SQLServer2000的系統(tǒng)信息。SELECT輸出常量或變量值時，在網(wǎng)格窗口用表格的形式輸出?！纠?-1】用SELECT語句輸出常量值：SELECT'sqlserver6.5',256*256該語句相當(dāng)于顯示兩個計算列，未指定別名則列標(biāo)題為“（無名列）”。也可使用語句：SELECT字符串常量='sqlserver6.5',計算結(jié)果=256*256該語句為兩個計算列指定了別名作列標(biāo)題。兩個語句分別執(zhí)行結(jié)果如圖5-1所示。5.1.2、使用SELECT語句進行無數(shù)據(jù)源檢索【例5-2】查看全局變量—本地SQLServer服務(wù)器的版本信息：SELECT@@version其中@@version為系統(tǒng)無參數(shù)存儲過程，也稱為全局變量。服務(wù)器的返回結(jié)果是：MicrosoftSQLServer2000-8.00.194(IntelX86)Aug6200000:57:48Copyright(c)1988-2000MicrosoftCorporationPersonalEditiononWindows4.10(Build2222:A)【例5-3】查詢本地SQLServer服務(wù)器使用的語言：SELECT@@language服務(wù)器的返回結(jié)果是：簡體中文5.1.3、指定字段列表及列別名（列標(biāo)題）字段列表用于指定查詢結(jié)果集中所需要顯示的列，可以使用以下格式：*指定所使用的全部數(shù)據(jù)表的全部字段表名.*多表查詢時指定某一個表的全部字段字段列表指定所需要顯示的列字段列表可以指定字段名也可以指定表達(dá)式（計算列），還可為字段列或計算列指定別名（列標(biāo)題），多個列之間用逗號隔開。指定顯示列的格式：[表名.]字段名|計算表達(dá)式|別名={[表名.]字段名|計算表達(dá)式}若指定別名時也可寫成ANSI標(biāo)準(zhǔn)的格式：{[表名.]字段名|計算表達(dá)式}[AS]別名5.1.3、指定字段列表及列別名（列標(biāo)題）說明：l

多表查詢時同名字段必須加表名作字段名前綴，單表或不重復(fù)字段可以不加。l

指定顯示的列可以單個字段，也可以是派生列—由多個字段運算后產(chǎn)生的列或是由表達(dá)式計算后產(chǎn)生的列—統(tǒng)稱為計算列。l

顯示字段列時若不指定別名則以字段名為查詢結(jié)果的列標(biāo)題，可以指定別名代替字段名作為查詢結(jié)果的列標(biāo)題。l

顯示計算列時若不指定別名則顯示為“（無名列）”，可指定別名作為顯示的列標(biāo)題。l

字段列表的順序是查詢結(jié)果的顯示順序，可以與數(shù)據(jù)表定義的字段順序不同。5.1.3、指定字段列表及列別名（列標(biāo)題）注意：u

別名就是表達(dá)式的名字，為字段指定別名相當(dāng)于表達(dá)式中只有一個字段變量。u

別名不允許出現(xiàn)在其他表達(dá)式當(dāng)中，當(dāng)某個表達(dá)式需要使用別名所代表的計算結(jié)果時，必須使用該原表達(dá)式或字段名?！纠?-4】查詢“進貨表2006”的全部記錄，只顯示“進貨日期，貨號，數(shù)量，供貨商ID”字段：SELECT供貨商ID,進貨日期,貨號,數(shù)量FROM進貨表2006注意：SELECT語句中指定的字段列表順序可以任意，不需要與表的定義順序一致。它只表示查詢結(jié)果集的顯示順序，不會對數(shù)據(jù)表的字段順序進行更改。5.1.4、指定查詢結(jié)果的顯示范圍指定查詢結(jié)果集中記錄的顯示范圍有三個選項：ALL顯示查詢結(jié)果集的全部記錄（默認(rèn)值）|DISTINCT[ROW]對查詢結(jié)果集過濾重復(fù)行|TOPn[percent]顯示查詢結(jié)果集開頭的n[%]個記錄說明：l

若不指定顯示范圍則默認(rèn)為ALL，顯示查詢結(jié)果集的全部記錄。l

ALL、DISTINCT、TOP三項參數(shù)必須單獨使用，不允許同時出現(xiàn)在一個SELECT語句中。5.1.4、指定查詢結(jié)果的顯示范圍【例5-5】查詢“銷售表2006”的記錄，按不同記錄顯示范圍顯示全部字段*：顯示全部查詢結(jié)果的記錄：SELECTALL*FROM銷售表2006在查詢記錄中去掉重復(fù)行：SELECTDISTINCT*FROM銷售表2006只顯示查詢結(jié)果前5條記錄：SELECTTOP5*FROM銷售表2006只顯示查詢結(jié)果前20%記錄：SELECTTOP20percent*FROM銷售表2006當(dāng)查詢結(jié)果的數(shù)據(jù)量非常龐大又沒有必要對所有數(shù)據(jù)進行瀏覽時，使用TOP指定顯示記錄的范圍可以大大減少查詢時間。5.1.4、指定查詢結(jié)果的顯示范圍【例5-6】查詢“銷售表2006”的記錄，只顯示字段“客戶名稱”，比較過濾重復(fù)行的效果：SELECT客戶名稱FROM銷售表2006SELECTDISTINCT客戶名稱FROM銷售表2006注意：u

如果使用語句：SELECTALLDISTINCT客戶名稱FROM銷售表2006或者想在查詢結(jié)果的前5條記錄中去掉重復(fù)行：SELECTTOP5DISTINCT客戶名稱FROM銷售表2006都會產(chǎn)生語法錯誤，因為ALL、DISTINCT、TOP不允許同時使用。u

使用DISTINCT關(guān)鍵字對多列字段查詢時將返回多列數(shù)據(jù)組合后的惟一記錄。5.1.4、指定查詢結(jié)果的顯示范圍

【例5-7】查詢“進貨表2006”的前5條記錄，只顯示“進貨日期，貨號，數(shù)量，供貨商ID”字段，使用別名顯示標(biāo)題“進貨日期，商品編號，數(shù)量，供貨商代碼”。SELECTTOP5供貨商代碼=供貨商ID,進貨日期,商品編號=貨號,數(shù)量FROM進貨表2006或者：SELECTTOP5供貨商ID供貨商代碼,進貨日期,貨號AS商品編號,數(shù)量FROM進貨表2006注意：u

用戶可使用任意的別名作為結(jié)果集的列標(biāo)題，或為計算列加上任意的標(biāo)題。u

在計算列表達(dá)式后面直接給出列名是ANSI規(guī)則的標(biāo)準(zhǔn)方法，AS可以省略，別名也可以加單引號。如：供貨商ID‘供貨商代碼’5.1.4、指定查詢結(jié)果的顯示范圍【例5-8】使用字段組合的計算列查詢“商品一覽表”，顯示“貨號，貨名，每件毛利，字段外數(shù)據(jù)”。USEdiannaoxsSELECT商品信息=貨號+','+貨名,每件毛利=參考價格*0.1,30*2+5字段外數(shù)據(jù),256*256FROM商品一覽表本例假設(shè)“每件毛利”為參考價格10%，“字段外數(shù)據(jù)”并沒有實際意義，只是為了說明語法。運行結(jié)果如圖5-3所示。

注意：u

在T-SQL的計算列表達(dá)式中，允許使用+、-、*、/、%以及位運算的邏輯運算符AND（&）、OR（|）、XOR（^）、NOT（~）以及字符串連接符（+）。u

字符型字段可以使用加號將幾個字段的數(shù)據(jù)連接輸出在一列中。如：貨號+貨名5.1.5、用WHERE子句查詢滿足條件的記錄格式：WHERE條件表達(dá)式功能：從查詢的數(shù)據(jù)集中挑選出符合條件的記錄。說明：l

WHERE子句必須緊跟在FROM子句后面。l

條件表達(dá)式用于指定被顯示記錄所滿足的查詢條件。注意：u

條件表達(dá)式中可以包含字段名，但不允許使用為某個字段或計算列指定的別名，必須使用原字段名或計算列的表達(dá)式，因為WHERE子句指定的內(nèi)容就是表達(dá)式。u

條件表達(dá)式的運算結(jié)果必須是邏輯值TRUE、FALSE、UNKNOWN。5.1.5、用WHERE子句查詢滿足條件的記錄條件表達(dá)式中可以使用上一章所介紹的邏輯運算符和表達(dá)式：1、比較運算符：>>==<<=<>!=!>!<2、邏輯運算符：not、and、or3、范圍運算符：[not]between起始值and終止值4、列表運算符：[not]in(值1,…,值n)5、模糊匹配運算符：[not]like‘通配符’通配符：%：代表0個或多個字符的任意字符串_：代表單個字符[abcd]：代表指定范圍內(nèi)的單個字符[^abc]：代表不在指定范圍內(nèi)的單個字符6、空值運算符：[not]isnull5.1.5、用WHERE子句查詢滿足條件的記錄7、用于子查詢的運算符：列表運算符ANY|ALL：與比較運算符配合對多個值進行任意的比較。存在邏輯運算符[not]exists：判斷檢查子查詢返回的結(jié)果集中是否包含有記錄。8、集合函數(shù)在5.3中詳細(xì)介紹?！纠?-9】在“銷售表2006”中查詢一次銷售額超過10000元的銷售記錄。USEdiannaoxsSELECT銷售員,銷售日期,貨名,單價,數(shù)量,金額FROM銷售表2006WHERE金額>=100005.1.5、用WHERE子句查詢滿足條件的記錄【例5-10】在“商品一覽表”中查詢參考價格下浮25%以后低于1000元的商品信息。SELECT貨號,貨名,規(guī)格,原參考價格=參考價格,下浮后價格=參考價格*0.75FROM商品一覽表WHERE參考價格*0.75<1000注意：空值NULL不參與數(shù)值的比較。【例5-11】在“銷售表2006”中查詢“陳剛”銷售金額低于10000元的銷售記錄。SELECT*FROM銷售表2006WHERE銷售員='陳剛'and金額<10000【例5-12】在“銷售表2006”中查詢2006年2月份的銷售記錄。SELECT*FROM銷售表2006WHERE銷售日期>='2006/2/1'and銷售日期<='2006-3-1'或者：SELECT*FROM銷售表2006WHERE銷售日期between'2006/2/1'and'2006-3-1'注意：未設(shè)置時間的日期默認(rèn)時間為：00：00：00，所以終值為2006年3月1日5.1.5、用WHERE子句查詢滿足條件的記錄【例5-13】在“銷售表2006”查詢銷售數(shù)量不在3到25之間的銷售記錄。SELECT*FROM銷售表2006WHERE數(shù)量notbetween3and25或者：SELECT*FROM銷售表2006WHERE數(shù)量<3or數(shù)量>25【例5-14】在“員工表”中查詢1980年出生的員工信息SELECT*FROM員工表WHERE出生日期between'1980/1/1'and'1981/1/1'或者：SELECT*FROM員工表WHEREyear(出生日期)=19801980作為日期處理時也可以加上單引號?！纠?-15】在“員工表”中查詢陳剛和高宏的資料。SELECT員工ID,姓名,性別,出生日期=Convert(char(12),出生日期,111),年齡=year(getdate())-year(出生日期),部門,工齡=CASt(year(getdate())-year(工作時間)ASvarchar(2))+'年'FROM員工表WHERE姓名='陳剛'or姓名='高宏'注意表達(dá)式中char()與varchar()的區(qū)別，會影響輸出列寬。5.1.5、用WHERE子句查詢滿足條件的記錄【例5-16】在“員工表”中查詢姓“于”的員工信息。SELECT*FROM員工表WHERE姓名like'于%'【例5-17】在“銷售表2006”中查詢?yōu)H坊與青島兩地的客戶方法一：SELECTDISTINCT客戶名稱FROM銷售表2006WHERE客戶名稱like'%濰坊%'or客戶名稱like'%青島%'方法二：SELECTDISTINCT客戶名稱,購貨日期=Convert(char(12),銷售日期,111),購買商品=貨名,數(shù)量,金額=Convert(varchar(10),金額)FROM銷售表2006WHERE客戶名稱like'%濰坊%'or客戶名稱like'%青島%'【例5-18】在“供貨商表”中查詢賬戶后4位數(shù)是7765的廠家信息。SELECT*FROM供貨商表WHERE賬戶like'%7765'【例5-19】在“銷售表2006”中查詢不知名(未輸入貨名，列值為空)商品的銷售記錄。SELECT*FROM銷售表2006WHERE貨名isnull運行代碼后在網(wǎng)格窗口沒有任何數(shù)據(jù)輸出—該字段沒有空值的記錄。5.1.6、用ORDERBY子句對查詢結(jié)果集排序

格式：ORDERBY{列名或別名[ASC|DESC]}[,…n]功能：按結(jié)果集中指定列的數(shù)值大小，排序后按ASC或DESC指定的順序顯示。l

ASC是默認(rèn)方式表示按升序排序可以省略，DESC表示降序排序。l

指定多列排序時，各列的先后順序決定排序的優(yōu)先級。l

如果對SELECT語句中的別名字段或計算列排序，如果沒用DISTINCT過濾重復(fù)行，則允許在ORDERBY中使用字段別名排序，否則只能使用原字段名或計算列表達(dá)式。l

如果SELECT語句中沒有指定GROUPBY分組，也沒用DISTINCT過濾重復(fù)行，則可指定不在SELECT字段列表中的字段排序，但該字段必須包含在FROM指定的數(shù)據(jù)源中。l

Ntext、Text或Image類型的列不允許排序。5.1.6、用ORDERBY子句對查詢結(jié)果集排序

u

使用GROUPBY分組時允許用別名，但不允許對SELECT沒有指定的字段排序。u

使用DISTINCT過濾重復(fù)行時，即不允許用別名也不允許對SELECT沒有指定的字段排序。u

ORDERBY的作用只是排列查詢結(jié)果集中的顯示順序，而不是對數(shù)據(jù)表排序。如果要對數(shù)據(jù)表排序可以創(chuàng)建索引對象，增加查詢速度?！纠?-20】按進貨“數(shù)量”升序排序查詢“進貨表2006”的記錄。USEdiannaoxsSELECT*FROM進貨表2006ORDERBY數(shù)量5.1.6、用ORDERBY子句對查詢結(jié)果集排序

【例5-21】按銷售“數(shù)量”降序排序查詢“銷售表2006”中“章曉曉”的銷售記錄。SELECT銷售員AS姓名,銷售日期,貨名商品名稱,數(shù)量FROM銷售表2006WHERE銷售員='章曉曉'ORDERBY數(shù)量DESC注意：WHERE子句中不允許使用別名，若寫成：WHERE姓名='章曉曉'是錯誤的。5.1.6、用ORDERBY子句對查詢結(jié)果集排序

【例5-22】按“年齡”降序、“姓名”升序排序查詢“員工表”的信息。SELECT員工ID,姓名,性別,出生日期=Convert(char(12),出生日期,111),年齡=year(getdate())-year(出生日期),部門FROM員工表ORDERBY年齡DESC,姓名注意：u

“年齡”是計算列的別名，WHERE子句不允許使用別名，但ORDERBY子句只要不與DISTINCT同時使用則可以使用別名，并按該計算列的值進行排序。也可以直接指定按計算表達(dá)式排序：ORDERBYyear(getdate())-year(出生日期)DESC,姓名u

多個字段排序時ORDERBY的書寫順序確定優(yōu)先級，本例“年齡”是第一級，在年齡相同的記錄中再按第二級“姓名”排序，依次可以有第三級、第四級……。u

對漢字的排序規(guī)則與《漢語詞典》中按拼音排列的順序相同，如“章”大于“孫”。5.1.6、用ORDERBY子句對查詢結(jié)果集排序

【例5-23】按“參考價格”升序排序查詢“商品一覽表”的部分信息。SELECT貨號,貨名,規(guī)格FROM商品一覽表ORDERBY參考價格由于SELECT語句中沒有用GROUPBY分組，也沒有用DISTINCT過濾重復(fù)行，所以可以指定對不在SELECT字段列表中的“參考價格”排序，雖然結(jié)果集中不顯示參考價格，但已按各自“參考價格”進行了排序，其中NULL被排在第一位。查詢結(jié)果如圖5-18所示。5.2SELECT多表連接查詢與創(chuàng)建新表

在第1章我們介紹了數(shù)據(jù)表的交叉連接、內(nèi)連接、外連接、自連接等4種方式，用于多個數(shù)據(jù)源的多表連接的FROM子句語法格式為：FROM表名1[IN數(shù)據(jù)庫名]{[連接方式]表名2[ON連接條件]}[…n]或：FROM表名列表WHERE連接條件5.2.1、交叉連接crossjoin又稱非限制連接、無條件連接或笛卡爾連接，就是將兩個表不加任何限制的組合在一起，連接結(jié)果是具有兩個表記錄數(shù)乘積的邏輯數(shù)據(jù)表。交叉連接沒有實際意義，僅用于說明表直接的連接原理。格式一：SELECT字段列表FROM表名1{CrossJoin表名2}[…n]格式二：SELECT字段列表FROM表名1,表名2[,…n]【例5-24】將“供貨商表”與“進貨表2006”進行交叉連接并觀察結(jié)果。USEdiannaoxsSELECT*FROM供貨商表,進貨表2006或者：SELECT*FROM供貨商表crossjoin進貨表2006其中“供貨商表”5條記錄，“進貨表2006”10條記錄，連接結(jié)果總共有50條記錄。5.2.2、內(nèi)連接[inner]join也叫自然連接，只將兩個表中滿足指定條件的記錄連接成一條新記錄，舍棄所有不滿足條件沒有進行連接的記錄。內(nèi)連接是數(shù)據(jù)表最常用的連接方式，其語法格式為：格式一：SELECT列名列表FROM表名1{[inner]Join表名2ON表名1.列名=表名2.列名}[…n]格式二：SELECT列名列表FROM表名1,表名2[,…]WHERE表名1.列名=表名2.列名[and…]5.2.2、內(nèi)連接[inner]join說明：l

當(dāng)表名太長時，一般可在FROM指定表的同時為表定義一個別名，定義格式為：表名[AS]別名（用AS或空格隔開）或：表名.別名l

如果兩個表有相同的字段名，在指定字段名時必須在列名前面加上表名（或表別名）作為前綴加以區(qū)別，用“表名.列名”或“表別名.列名”表示。l

如果列名是某個表中單獨具有的，可以不加前綴，但加上表名會增強可讀性。注意：為表名定義別名后，在SELECT及各個子句中指定字段時必須使用“別名.列名”的格式，不允許再使用“表名.列名”。5.2.2、內(nèi)連接[inner]join【例5-25】將“供貨商表”與“進貨表2006”進行簡單的內(nèi)連接。USEdiannaoxsSELECT*FROM供貨商表ASgjoin進貨表2006ASjONg.供貨商ID=j.供貨商ID或：SELECT*FROM供貨商表ASg,進貨表2006ASjWHEREg.供貨商ID=j.供貨商ID該語句將貨號相等的記錄連接起來顯示兩個表的全部字段。5.2.2、內(nèi)連接[inner]join【例5-26】將“商品一覽表”與“銷售表2006”進行內(nèi)連接，顯示完整的銷售表。SELECT銷售日期=convert(varchar(12),銷售日期,111),客戶名稱,s.貨號,s.貨名,s.規(guī)格,s.單位,單價=convert(varchar(10),單價),數(shù)量,金額=convert(varchar(10),金額,1),銷售員FROM商品一覽表sJOIN銷售表2006xONs.貨號=x.貨號注意：u

內(nèi)連接時兩個表的先后順序任意（外連接則區(qū)分左右）。u

兩個表共有的字段若內(nèi)容相同，可任選其一，但表名前綴不能省略。5.2.2、內(nèi)連接[inner]join

【例5-27】將“供貨商表”“進貨表2006”“商品一覽表”三個表進行內(nèi)連接。SELECT進貨日期=convert(varchar(12),進貨日期,111),s.貨號,s.貨名,s.規(guī)格,s.單位,數(shù)量,進價=convert(varchar(10),進價),供貨商,發(fā)貨人=聯(lián)系人,收貨人FROM供貨商表ASgjoin進貨表2006jONg.供貨商ID=j.供貨商IDjoin商品一覽表sONj.貨號=s.貨號或：SELECT進貨日期=convert(varchar(12),進貨日期,111),s.貨號,s.貨名,s.規(guī)格,s.單位,數(shù)量,進價=convert(varchar(10),進價),供貨商,發(fā)貨人=聯(lián)系人,收貨人FROM供貨商表ASg,進貨表2006j,商品一覽表sWHEREg.供貨商ID=j.供貨商IDandj.貨號=s.貨號5.2.3、外連接left|right|full[outer]join

在內(nèi)連接（自然連接）中，必須是兩個表中匹配的記錄才能在結(jié)果集中出現(xiàn)。而外連接只限制一個表，對另一個表不加限制（所有的行都可出現(xiàn)在結(jié)果集中），以便在結(jié)果集中保證該表的完整性。外連接分為左外連接、右外連接、全外連接三種。1、左外連接左外連接返回左表(表名1)的全部記錄及右表相關(guān)的信息。左外連接取左表的全部記錄按指定條件與右表中滿足條件的記錄進行連接，若右表中沒有滿足條件的記錄則在相應(yīng)字段填入NULL（Bit位類型字段填0）。但條件不限制左表，左表的全部記錄都包括在結(jié)果集中，以保持左表的完整性。SELECT列名列表FROM表名1left[outer]join表名2ON表名1.列名=表名2.列名1、左外連接【例5-28】用左外連接查詢“供貨商表”與“進貨表2006”，獲取生產(chǎn)廠家提供貨物的品種（貨號）、供貨日期和供貨數(shù)量。SELECT生產(chǎn)廠家=g.供貨商,j.貨號,供貨日期=convert(varchar(15),j.進貨日期,111),供貨數(shù)量=j.數(shù)量,貨款金額=convert(varchar(10),j.進價*j.數(shù)量),j.收貨人FROM供貨商表ASgleftjoin進貨表2006jONg.供貨商ID=j.供貨商ID注意：u

左外連接默認(rèn)按左表的主鍵順序排序。u

左外連接可以保證左表的完整性，在查詢結(jié)果中明顯看到還沒有進貨的廠家，該廠家的進貨數(shù)據(jù)為NULL，說明還沒有業(yè)務(wù)發(fā)生。2、右外連接右外連接返回右表的全部記錄及左表相關(guān)的信息。右外連接與左外連接相同，只是把兩個表的順序顛倒了一下，就是取右表的全部記錄按指定條件與左表中滿足條件的記錄進行連接，若左表中沒有滿足條件的記錄則在相應(yīng)字段填入NULL（Bit位類型字段填0），右表的全部記錄都在結(jié)果集中，保持右表的完整性。SELECT列名列表FROM表名1right[outer]join表名2ON表名1.列名=表名2.列名注意：右外連接與左外連接只是表的順序不一樣，如果把左外連接中表的順序變一下，再使用右外連接，其結(jié)果是相同的。2、右外連接【例5-29】將“銷售表2006”與“商品一覽表”右外連接，查詢銷售產(chǎn)品與公司所經(jīng)營產(chǎn)品的對應(yīng)情況。SELECT銷售日期=convert(varchar(12),銷售日期,111),客戶名稱,s.貨號,s.貨名,s.規(guī)格,s.單位,s.庫存量,單價=convert(varchar(10),單價),數(shù)量,金額=convert(varchar(10),金額,1)FROM銷售表2006xrightjoin商品一覽表sONs.貨號=x.貨號

注意：u

右外連接默認(rèn)按右表的主鍵“貨號”順序排序，若增加“ORDERBY銷售日期”則順序與“銷售日期”一致，沒銷售的產(chǎn)品（日期為NULL）將排在最前面。u

右外連接保證右表的完整性，查詢結(jié)果可看出未銷售的產(chǎn)品（銷售信息為NULL）。u

如果計算了庫存量之后就可看到第5條記錄有庫存沒銷售，第12條記錄庫存為0說明還沒有進貨，所以沒有銷售。3、全外連接全外連接返回左表與右表的全部記錄。全外連接相當(dāng)于先進行左外連接再進行右外連接的綜合連接，就是取左表的全部記錄按指定條件與右表中滿足條件的記錄進行連接。右表中不滿足條件的記錄則在相應(yīng)字段填入NULL，再將右表不滿足條件的記錄列出，在左表不符合條件記錄的相應(yīng)字段填入NULL。全外連接使兩個表的全部記錄都包括在結(jié)果集中，可以保持兩個表的完整性。語法格式：SELECT列名列表FROM表名1full[outer]join表名2ON表名1.列名=表名2.列名3、全外連接【例5-30】使用全外連接查詢“供貨商表”與“進貨表2006”，獲取生產(chǎn)廠家給提供供貨的品種、供貨日期和供貨數(shù)量。SELECT生產(chǎn)廠家=g.供貨商,j.貨號,供貨日期=convert(varchar(15),j.進貨日期,111),供貨數(shù)量=j.數(shù)量,貨款金額=convert(varchar(10),j.進價*j.數(shù)量),j.收貨人FROM供貨商表ASgfulljoin進貨表2006jONg.供貨商ID=j.供貨商ID注意：u

該查詢結(jié)果與【例5-28】圖5-21左連接時完全一致，明顯看到已建立廠家信息尚未進貨的廠家。u

假設(shè)已經(jīng)進貨卻還沒有登記廠家信息，在全外連接中也會一目了然（但設(shè)置了外鍵這種情況是不可能發(fā)生的）。5.2.4、自內(nèi)連接join自內(nèi)連接簡稱自連接，是一張表自己對自己的內(nèi)連接，即在一張表的兩個副本之間進行內(nèi)連接。用自連接可以將同一個表的不同行連接起來。使用自連接時，必須為兩個副本指定別名，使之在邏輯上成為兩個表。語法格式：SELECT列名列表FROM表名[AS]別名1join表名.別名2ON別名1.列名=別名2.列名5.2.4、自內(nèi)連接join【例5-31】使用自連接在“進貨表2006”中找出已經(jīng)進貨2次及2次以上的廠家。USEdiannaoxsSELECTDISTINCTg1.供貨商ID,g1.貨號,供貨日期1=convert(varchar(15),g1.進貨日期,111),供貨數(shù)量=g1.數(shù)量,貨款金額=convert(varchar(10),g1.進價*g1.數(shù)量)FROM進貨表2006ASg1join進貨表2006ASg2ONg1.供貨商ID=g2.供貨商IDandg1.貨號<>g2.貨號ORDERBYg1.供貨商ID,convert(varchar(15),g1.進貨日期,111)查詢結(jié)果如圖5-23所示。可見其中只有4個進貨兩次以上的廠家及有關(guān)的信息，另外的廠家SHKD只進貨一次，SDKJ沒有進貨則被篩除了。5.2.4、自內(nèi)連接join注意：u自連接雖然使用一個表但有兩個拷貝，在邏輯上是兩個表而且字段完全相同，因此字段列表中字段名必須加上其中一個表的別名做前綴。u使用自連接會產(chǎn)生許多重復(fù)行，一般加關(guān)鍵字DISTINCT過濾掉重復(fù)行。u自連接默認(rèn)按ON使用的連接字段排序（供貨商ID，貨號），為了按廠家順序再按進貨日期排序，本例使用了ORDERBY指定排序。u由于使用了DISTINCT，所以不允許使用字段列表沒有指定的“g1.進貨日期”排序，也不允許使用別名“供貨日期”進行排序，本例使用了字段列表中的表達(dá)式。5.2.5、使用INTO子句創(chuàng)建新表語法格式：INTO新表名使用SELECTINTO語句首先創(chuàng)建一個新表，然后用查詢的結(jié)果填充新表，也就是說SELECT語句只要加上INTO子句，其查詢結(jié)果集都不再顯示而被添加到創(chuàng)建的新表中。l

INTO子句必須是SELECT語句的第一個子句，緊跟在字段列表之后。l

用INTO創(chuàng)建的新表可以是以‘#’開頭的臨時表，也可以是永久表，但新表中的字段沒有原表字段上綁定的約束對象。l

用戶在執(zhí)行帶INTO的SELECT語句時，必須擁有創(chuàng)建表的權(quán)限。l

INTO子句不能與COMPUTE子句一起使用。l

SELECTINTO可將幾個表或視圖中的數(shù)據(jù)組合成一個表。l

SELECTINTO可創(chuàng)建一個包含選自鏈接服務(wù)器的新表。5.2.5、使用INTO子句創(chuàng)建新表我們可以回顧一下上一章簡單介紹的數(shù)據(jù)表復(fù)制創(chuàng)建了：SELECT*|字段列表INTO新表名

FROM源表名[WHERE條件表達(dá)式]l

使用*復(fù)制的新表與源表字段完全相同，用“字段列表”可以選擇部分字段。l

使用WHERE可以有選擇的復(fù)制部分記錄，只有滿足條件的記錄才被復(fù)制，省略WHERE則連同全部數(shù)據(jù)一起復(fù)制。l

使用恒為假的條件“WHERE1=2”則沒有記錄，只復(fù)制一個具有指定字段的空表。l

原表字段上綁定的約束不能被復(fù)制。前面我們所介紹的所有例題，只要在SELECT之后加上INTO子句，指定一個合法惟一的表名字，其查詢結(jié)果都將被創(chuàng)建在指定的新表中?！緦嵗毩?xí)5-1】

【實例練習(xí)5-1】2006年度結(jié)束時創(chuàng)建《銷售表2007》《進貨表2007》SELECT*INTO銷售表2007FROM銷售表2006WHERE1=2SELECT*INTO進貨表2007FROM進貨表2006WHERE1=2再將《銷售表2007》《進貨表2007》設(shè)置各種約束，在2007年就可以使用這兩個新表了。注意：SQLServer2000的用戶自定義數(shù)據(jù)類型（見第7章）實際上就是在系統(tǒng)已有的類型上綁定各種規(guī)則、默認(rèn)值等約束對象，如果我們事先創(chuàng)建好綁定約束對象的自定義數(shù)據(jù)類型，在創(chuàng)建《銷售表2006》時使用這些自定義數(shù)據(jù)類型，則用SELECTINTO創(chuàng)建的《銷售表2007》就與《銷售表2006》完全相同了。在【實例練習(xí)5-4】中我們將使用《銷售表2007》。5.3用SELECT語句對數(shù)據(jù)進行統(tǒng)計匯總

5.3.1、集合函數(shù)（聚合函數(shù)、統(tǒng)計函數(shù)）為了有效處理查詢得到的數(shù)據(jù)集合，SQLServer提供了一系列統(tǒng)計函數(shù)。這些函數(shù)可以把存儲在數(shù)據(jù)庫中的數(shù)據(jù)匯總為一個整體而不再是一行行單獨的記錄，SELECT語句使用這些函數(shù)可實現(xiàn)數(shù)據(jù)集合的匯總及統(tǒng)計運算。avg([ALL|DISTINCT]列名)求指定數(shù)字字段的平均值sum([ALL|DISTINCT]列名)求指定數(shù)字字段的總和max([ALL|DISTINCT]列名)求指定數(shù)字字段的最大值min([ALL|DISTINCT]列名)求指定數(shù)字字段的最小值count([ALL|DISTINCT]列名)求滿足條件記錄中指定字段不為空的記錄個數(shù)count(*)求滿足條件的記錄總數(shù)（包括空值的記錄個數(shù)）5.3.1、集合函數(shù)（聚合函數(shù)、統(tǒng)計函數(shù)）說明：l列名是函數(shù)指定的統(tǒng)計對象，可以是多列組成的表達(dá)式也可以是計算列的別名，l使用ALL指定全部記錄范圍(默認(rèn))，ALL完全可以省略。l使用DISTINCT則在計算之前先消除重復(fù)的值再匯總，也隱含ALL全部范圍。lcount(*)可以包括空值記錄，其他函數(shù)均不統(tǒng)計空值記錄—忽略空值。注意：u集合函數(shù)使用DISTINCT時則不允許使用計算列或字段的別名。u集合函數(shù)將查詢結(jié)果集統(tǒng)計為單一數(shù)據(jù)，即匯總為一條記錄，在SELECT中使用了集合函數(shù)就不允許再指定字段名，用GROUPBY指定的字段除外。5.3.1、集合函數(shù)（聚合函數(shù)、統(tǒng)計函數(shù)）【例5-32】計算“商品一覽表”中已制定參考價格商品的總值、平均價、最高價、最低價、已定價商品個數(shù)，總商品個數(shù)。USEdiannaoxsSELECTsum(參考價格),avg(參考價格),max(參考價格),min(參考價格),count(參考價格),count(*)FROM商品一覽表SELECTsum(參考價格)總價格,avg(參考價格)AS平均價,最高價=max(參考價格),最低價=min(參考價格),定價商品數(shù)=count(參考價格),總商品數(shù)=count(*)FROM商品一覽表

注意：同一SELECT語句中各集合函數(shù)可以各自指定自己要統(tǒng)計的字段。如count(*)也可使用count(貨號)。5.3.1、集合函數(shù)（聚合函數(shù)、統(tǒng)計函數(shù)）【例5-33】對參考價格下浮10%后進行統(tǒng)計。SELECTavg(參考價格*0.9)AS平均價,最高價=max(參考價格*.9),最低價=min(參考價格*.9),定價商品數(shù)=count(參考價格)FROM商品一覽表其中count(參考價格)也可寫成count(參考價格*.9)5.3.1、集合函數(shù)（聚合函數(shù)、統(tǒng)計函數(shù)）【例5-34】使用WHERE指定記錄條件，統(tǒng)計計算機整機類商品（貨號第一位數(shù)字1）的平均價、最高價、最低價、已定價商品個數(shù)，總商品個數(shù)。SELECTavg(參考價格)AS整機類平均價,最高價=max(參考價格),最低價=min(參考價格),定價商品數(shù)=count(參考價格),總商品數(shù)=count(*)FROM商品一覽表WHERE貨號like'1%'注意：條件最好不要使用“貨名=‘計算機’”因為該類整機的名稱可能不同，若所有整機名稱中都包括“計算機”三個字時可以使用“貨名=‘%計算機%’”5.3.1、集合函數(shù)（聚合函數(shù)、統(tǒng)計函數(shù)）【例5-35】使用DISTINCT關(guān)鍵字統(tǒng)計2006年已經(jīng)銷售的商品種類。SELECT已銷售商品種類=count(DISTINCT貨號)FROM銷售表2006查詢結(jié)果顯示已銷售商品種類為7，若不使用DISTINCT結(jié)果為14。5.3.2、用GROUPBY子句對記錄分類統(tǒng)計匯總

GROUPBY分組字段名列表[HAVING條件表達(dá)式]功能：按指定條件對指定字段依次分組進行統(tǒng)計匯總說明：l

使用GROUPBY子句時，SELECT指定的字段必須包含且只能包含GROUPBY子句中指定的分組字段（可以為它指定別名），其他必須是由集合函數(shù)組成的一個或多個計算列，統(tǒng)計函數(shù)中所使用的列不受限制。l

GROUPBY子句中不允許使用字段或計算列的別名，可直接使用表達(dá)式。l

GROUPBY子句指定表達(dá)式時，SELECT指定的字段中可以不包括該表達(dá)式，l

HAVING子句用于指定統(tǒng)計結(jié)果所要滿足的條件，表達(dá)式中可以直接使用計算列的表達(dá)式而不允許使用別名。l

HAVING子句必須配合GROUPBY子句使用，且設(shè)置的條件必須與GROUPBY子句指定的分組字段有關(guān)。5.3.2、用GROUPBY子句對記錄分類統(tǒng)計匯總

注意：u使用GROUPBY的SELECT語句仍可使用ORDERBY子句對統(tǒng)計結(jié)果排序，但必須在GROUPBY之后，可以使用別名但不允許對SELECT沒指定的列排序。uHAVING子句是對分組統(tǒng)計后的查詢結(jié)果進行篩選，在統(tǒng)計結(jié)果中選擇滿足條件的記錄作為統(tǒng)計匯總后的結(jié)果集。u使用GROUPBY的SELECT語句仍可使用WHERE子句指定條件，但WHERE子句是在分組前對原表記錄進行篩選，使?jié)M足條件的記錄參加分組統(tǒng)計。5.3.2、用GROUPBY子句對記錄分類統(tǒng)計匯總

【例5-36】按貨名分類統(tǒng)計同類商品的總數(shù)量及平均價格。SELECT貨名,商品數(shù)量=count(貨號),平均價格=avg(參考價格)FROM商品一覽表GROUPBY貨名注意：ucount()也可使用“規(guī)格”等同類商品中不重復(fù)的字段，使用“*”則包含NULL。u查詢結(jié)果默認(rèn)按貨名排序，可指定按降序排序；也可指定按“商品數(shù)量”或“平均價格”別名排序，但不允許對“貨號”排序，因為使用GROUPBY時不允許對SELECT沒有指定的字段排序。uSELECT指定的字段如果沒有“貨名”字段，或使用GROUPBY子句沒有指定的列，則會出現(xiàn)語法錯誤。如以下語句都是錯誤的：SELECT商品數(shù)量=count(貨號),平均價格=avg(參考價格)FROM商品一覽表GROUPBY貨名SELECT貨號，貨名,商品數(shù)量=count(貨號),平均價格=avg(參考價格)FROM商品一覽表GROUPBY貨名5.3.2、用GROUPBY子句對記錄分類統(tǒng)計匯總

【例5-37】統(tǒng)計公司員工人數(shù)、平均年齡、最大年齡、最小年齡、平均工齡、最長工齡和最短工齡。

SELECT職工人數(shù)=count(*),平均年齡=CASt(avg(year(getdate())-year(出生日期))ASvarchar(2))+'歲',最大年齡=max(year(getdate())-year(出生日期)),最小年齡=min(year(getdate())-year(出生日期)),平均工齡=CASt(avg(year(getdate())-year(工作時間))ASvarchar(2))+'年',最長工齡=max(year(getdate())-year(工作時間)),最短工齡=min(year(getdate())-year(工作時間))

FROM員工表5.3.2、用GROUPBY子句對記錄分類統(tǒng)計匯總

【例5-37】也可按部門分類統(tǒng)計“員工表”各部門員工人數(shù)及平均年齡。SELECT部門,count(*),CASt(avg(year(getdate())-year(出生日期))ASvarchar(2))+'歲'平均年齡，max(year(getdate())-year(出生日期))最大年齡,min(year(getdate())-year(出生日期))最小年齡,CASt(avg(year(getdate())-year(工作時間))ASvarchar(2))+'年'平均工齡,max(year(getdate())-year(工作時間))最長工齡,min(year(getdate())-year(工作時間))最短工齡

FROM員工表GROUPBY部門讀者可以將“（無名列）”命名為“員工人數(shù)”。

5.3.2、用GROUPBY子句對記錄分類統(tǒng)計匯總

【例5-38】在“進貨表2006”中按“供貨商ID”分類統(tǒng)計從各廠家進貨的次數(shù)、總數(shù)量及進貨總價格。SELECT廠家編號=供貨商ID,進貨次數(shù)=count(供貨商ID),sum(數(shù)量)進貨總數(shù),sum(數(shù)量*進價)總貨款FROM進貨表2006GROUPBY供貨商ID【例5-39】按貨號分類統(tǒng)計“銷售表2006”中各種商品的銷售總數(shù)量、平均價格、最高價、最低價以及銷售總金額。SELECT貨號,銷售總數(shù)量=sum(數(shù)量),平均價格=avg(單價),最高價=max(單價),最低價=min(單價),銷售總金額=sum(金額)FROM銷售表2006GROUPBY貨號5.3.2、用GROUPBY子句對記錄分類統(tǒng)計匯總

【例5-40】按貨名分類統(tǒng)計“銷售表2006”中不包括計算機整機的各種商品的銷售總數(shù)量、平均價格以及銷售總金額。SELECT貨名,銷售總數(shù)量=sum(數(shù)量),平均價格=avg(單價),銷售總金額=sum(金額)FROM銷售表2006WHERE貨名<>'計算機'GROUPBY貨名或：SELECT貨名,銷售總數(shù)量=sum(數(shù)量),平均價格=avg(單價),銷售總金額=sum(金額)FROM銷售表2006GROUPBY貨號HAVING貨名<>'計算機'注意：由于使用“貨名”字段分類，WHERE先將商品“計算機”過濾掉再分組統(tǒng)計，而HAVING是在分組統(tǒng)計結(jié)果中再將“計算機”過濾掉，兩種方法結(jié)果一樣。5.3.2、用GROUPBY子句對記錄分類統(tǒng)計匯總

【例5-40】如果使用“貨號”分類，使用WHERE在統(tǒng)計之前仍然可以將“計算機”商品過濾掉：SELECT貨號,銷售總數(shù)量=sum(數(shù)量),平均價格=avg(單價),銷售總金額=sum(金額)FROM銷售表2006WHERE貨名<>'計算機'GROUPBY貨號注意：使用“貨號”分類時，HAVING卻無法將“計算機”過濾掉，因為在分組統(tǒng)計結(jié)果中找不到商品名稱“計算機”，以下SQL語句是錯誤的：SELECT貨號,銷售總數(shù)量=sum(數(shù)量),平均價格=avg(單價),銷售總金額=sum(金額)FROM銷售表2006GROUPBY貨號HAVING貨名<>'計算機'如果使用“HAVING貨號notlike'1%'”則查詢結(jié)果與圖5-34相同。5.3.2、用GROUPBY子句對記錄分類統(tǒng)計匯總

【例5-41】按貨號分類統(tǒng)計“銷售表2006”中銷售總量大于10的商品銷售總數(shù)量、平均價格以及銷售總金額。SELECT貨號,銷售總量=sum(數(shù)量),平均價格=avg(單價),總金額=sum(金額)FROM銷售表2006GROUPBY貨號HAVINGsum(數(shù)量)>10注意：u

不能使用“HAVING銷售總量>10”，HAVING子句不允許使用別名。u

也不能使用“WHEREsum(數(shù)量)>10”，在統(tǒng)計之前是無法確定sum(數(shù)量)的。5.3.2、用GROUPBY子句對記錄分類統(tǒng)計匯總

【例5-42】在“銷售表2006”中按客戶名稱分類統(tǒng)計各客戶的購貨總數(shù)量、單筆最大量、單筆最小量、平均價格及購貨總金額。SELECT客戶名稱,購貨總量=sum(數(shù)量),單筆最大量=max(數(shù)量),單筆最小量=min(數(shù)量),平均價格=convert(varchar(10),avg(單價)),購貨總金額=convert(varchar(10),sum(金額),1)FROM銷售表2006GROUPBY客戶名稱查詢結(jié)果如圖5-36所示。

還可以設(shè)置條件“HAVINGsum(數(shù)量)>20”對統(tǒng)計結(jié)果進行過濾，如圖5-37所示。

5.3.2、用GROUPBY子句對記錄分類統(tǒng)計匯總

【例5-43】在“銷售表2006”中按客戶名稱和所購商品種類組合分類，統(tǒng)計各客戶同一類商品的購貨總數(shù)量、平均價、最高價、最低價及購貨總金額。SELECT客戶名稱,貨號,購貨總量=sum(數(shù)量),平均價=avg(單價),最高價=max(單價),最低價=min(單價),購貨總金額=sum(金額)FROM銷售表2006GROUPBY客戶名稱,貨號ORDERBY客戶名稱注意：第一分組是客戶名稱,相同的客戶再按貨號分組，結(jié)果集默認(rèn)按主鍵排序，不符合我們分類的要求，因此設(shè)置了“ORDERBY客戶名稱”指定按客戶排序。5.3.2、用GROUPBY子句對記錄分類統(tǒng)計匯總

【例5-44】在“銷售表2006”中按日期分類統(tǒng)計每天的商品日銷售量、單筆最大金額、單筆最小金額和每日總銷售額。SELECT銷售日期=convert(varchar(12),銷售日期,111),日銷售量=sum(數(shù)量),單筆最大金額=convert(varchar(10),max(金額)),單筆最小金額=convert(varchar(10),min(金額)),每日總銷售額=convert(varchar(10),sum(金額),1)FROM銷售表2006GROUPBY銷售日期5.3.2、用GROUPBY子句對記錄分類統(tǒng)計匯總

【例5-45】在“銷售表2006”中分類統(tǒng)計各銷售員的業(yè)績：銷售數(shù)量、單筆最大數(shù)量、單筆最大金額、三個月平均日營業(yè)額、總營業(yè)額，并按總營業(yè)額降序排序。SELECT銷售員,銷售數(shù)量=sum(數(shù)量),單筆最大數(shù)量=max(數(shù)量),單筆最大金額=convert(varchar(10),max(金額)),日營業(yè)額=convert(varchar(10),sum(金額)/90),總營業(yè)額=convert(varchar(10),sum(金額),1)FROM銷售表2006GROUPBY銷售員ORDERBY總營業(yè)額DESC5.3.2、用GROUPBY子句對記錄分類統(tǒng)計匯總

【例5-46】在“進貨表2006”中按“貨號”“進價”分類統(tǒng)計相同貨號不同價格的進貨次數(shù)和進貨數(shù)量。SELECT貨號,進價,進貨次數(shù)=count(*),總數(shù)量=sum(數(shù)量)FROM進貨表2006GROUPBY貨號,進價ORDERBY貨號注意：分組字段會過濾NULL，3002號商品“進價”為NULL將被忽略。5.3.3、綜合舉例練習(xí)【例5-47】在“銷售表2006”中按月份分類統(tǒng)計每月的商品月銷售量、單筆最大金額、單筆最小金額、平均價格和每月總銷售額。方法一：先用需要匯總的字段復(fù)制創(chuàng)建一個臨時表“#銷售月份表”，利用函數(shù)把“銷售日期”轉(zhuǎn)換成相應(yīng)的“月份”字段，然后對“#銷售月份表”按月份分組統(tǒng)計。注意：#開頭的表為臨時表，數(shù)據(jù)庫關(guān)閉時自動刪除，否則為永久表。SELECT月份=Month(銷售日期),數(shù)量,單價,金額INTO#銷售月份表FROM銷售表2006SELECT月份,月銷售量=sum(數(shù)量),單筆最大金額=convert(varchar(10),max(金額)),單筆最小金額=convert(varchar(10),min(金額)),平均價格=avg(單價),總銷售額=convert(varchar(10),sum(金額),1)FROM#銷售月份表GROUPBY月份5.3.3、綜合舉例練習(xí)方法二：不使用臨時表，直接對表達(dá)式Month(銷售日期)分組，以下語句結(jié)果相同：SELECT月份=Month(銷售日期),月銷售量=sum(數(shù)量),單筆最大金額=convert(varchar(10),max(金額)),單筆最小金額=convert(varchar(10),min(金額)),平均價格=avg(單價),總銷售額=convert(varchar(10),sum(金額),1)FROM銷售表2006GROUPBYMonth(銷售日期)注意：u不使用臨時表時，不允許使用“GROUPBY月份”，必須使用“GROUPBYMonth(銷售日期)”。uGROUPBY使用表達(dá)式時SELECT中也可以不指定“月份=Month(銷售日期)”字段。5.3.3、綜合舉例練習(xí)【例5-48】用內(nèi)連接對兩個表組合分類對《進貨表2006》中的廠家“供貨商ID”和《商品一覽表》的“貨名”分類，統(tǒng)計從廠家進貨的商品名稱，進貨次數(shù)，總數(shù)量，貨款總額。SELECT供貨商ID,貨名,進貨次數(shù)=count(*),總數(shù)量=sum(數(shù)量),貨款總額=convert(varchar(10),sum(數(shù)量*進價))FROM進貨表2006jjoin商品一覽表sONj.貨號=s.貨號GROUPBYj.供貨商ID,s.貨名ORDERBY供貨商ID或：SELECT供貨商ID,貨名,進貨次數(shù)=count(*),總數(shù)量=sum(數(shù)量),貨款總額=convert(varchar(10),sum(數(shù)量*進價))FROM進貨表2006j,商品一覽表sWHEREj.貨號=s.貨號GROUPBYj.供貨商ID,s.貨名ORDERBY供貨商ID【實例練習(xí)5-2】

使用《進貨表2006》的查詢結(jié)果簡單更新《商品一覽表》的平均進價。如果計算商品的銷售毛利潤，應(yīng)該對同一個貨號的商品按“(銷售價格-進貨價格)*銷售數(shù)量”進行計算，但同一商品不同廠家的進貨價格不同、不同時間同一廠家的同一商品進貨價格也會不同，如果準(zhǔn)確計算可以把不同進價的同一商品作為不同種類（不同貨號），但這樣會使商品種類無形增多，給商品的管理帶來不便。我們采用平均進貨價格的簡單方法處理同一種商品，但是這里所說的“平均進價”不是價格的簡單平均值，是加權(quán)平均。計算公式為：平均進價=總進貨金額/總進貨量其中：總進貨金額=(進貨價格1*進貨數(shù)量1)+(進貨價格2*進貨數(shù)量2)+…總進貨量=進貨數(shù)量1+進貨數(shù)量2+…【實例練習(xí)5-2】

若UPDATE更新表達(dá)式中使用多表字段時，要求多表之間必須是一對一的關(guān)系（不是一對一關(guān)系必須使用子查詢），以保證表達(dá)式有確定的值。但《進貨表》中的貨號不是關(guān)鍵字，同一個貨號商品的進貨記錄有多條，即總進貨金額和總進貨量不是來自一條記錄，無法對平均進價進行一對一計算更新。我們可以按貨號分類匯總創(chuàng)建貨號惟一的臨時表“#進貨量表”，再用這個表的數(shù)據(jù)與“商品一覽表”按一對一關(guān)系更新“平均進價”。USEdiannaoxsSELECT貨號,總進貨量=sum(數(shù)量),總進貨金額=sum(數(shù)量*進價)INTO#進貨量表FROM進貨表2006GROUPBY貨號UPDATE商品一覽表SET平均進價=j.總進貨金額/j.總進貨量FROM#進貨量表jWHERE商品一覽表.貨號=j.貨號SELECT*FROM商品一覽表在【實例練習(xí)5-6】中我們再用子查詢計算已有商品的“平均進價”，在第8章為《進貨表》創(chuàng)建觸發(fā)器，每次進貨時都會自動計算《商品一覽表》的“平均進價”。

【實例練習(xí)5-3】

用《進貨表2006》和《銷售表2006》的查詢結(jié)果簡單更新《商品一覽表》的“庫存量”。簡單計算庫存量公式：庫存量=總進貨量-總銷售量在《銷售表》和《進貨表》中，貨號都不是關(guān)鍵字，總進貨量和總銷售量是多條記錄的計算結(jié)果，無法與庫存量按相同貨號進行一對一計算。我們可以按貨號分類匯總創(chuàng)建貨號惟一的臨時表“#銷售量表”，再與【實例練習(xí)5-2】剛創(chuàng)建的“#進貨量表”進行全外連接，創(chuàng)建一個“#進貨銷售量表”，最后用UPDATE語句更新《商品一覽表》的“庫存量”。為什么不用“#銷售量表”“#進貨量表”與“商品一覽表”通過內(nèi)連接直接計算，還要用“#銷售量表”與“#進貨量表”進行全外連接再創(chuàng)建一個“#進貨銷售量表”呢？這是因為進貨量和銷售量分別來自兩個表，已進貨的商品(有記錄)可能還沒銷售(無記錄)；若是原庫存商品則有銷售而無新的進貨記錄，則在計算“總進貨量-總銷售量”時會因為其中一項不存在而出現(xiàn)錯誤?！緦嵗毩?xí)5-3】

使用全外連接可以保證兩個表的完整性，任一個表中不存在的記錄都會被填充NULL，我們將NULL改為數(shù)值0，再計算“庫存量=總進貨量-總銷售量”就不會出錯了。

用已有記錄簡單計算庫存量的代碼如下：USEdiannaoxsSELECT貨號,總銷售量=sum(數(shù)量)INTO#銷售量表FROM銷售表2006GROUPBY貨號SELECT貨號1=j.貨號,貨號2=x.貨號,j.總進貨量,x.總銷售量INTO#進貨銷售量表FROM#進貨量表jfulljoin#銷售量表xONj.貨號=x.貨號/*以下語句將有進貨未銷售或有銷售未進貨的NULL更新為相應(yīng)貨號和數(shù)量0；但如果銷售量大于進貨量則庫存為負(fù)值，更新庫存量時會出現(xiàn)錯誤—必須有約束保證*/【實例練習(xí)5-3】

UPDATE#進貨銷售量表SET貨號1=貨號2,總進貨量=0WHERE貨號1ISNULLUPDATE#進貨銷售量表