SQL匯總和分組數(shù)據(jù)

1、使用聚合函數(shù)進行匯總和分組SQL提供一組聚合函數(shù)，它們能夠?qū)φ麄€數(shù)據(jù)集合進行計算，將一組原始數(shù)據(jù)轉(zhuǎn)換為有用的信息，以便用戶使用。例如求成績表中的總成績、學生表中平均年齡等。SQL的聚合函數(shù)如表1所示。表1 聚合函數(shù)聚合函數(shù)支持的數(shù)據(jù)類型功 能 描 述Sum()數(shù)字對指定列中的所有非空值求和avg()數(shù)字對指定列中的所有非空值求平均值min()數(shù)字、字符、日期返回指定列中的最小數(shù)字、最小的字符串和最早的日期時間max()數(shù)字、字符、日期返回指定列中的最大數(shù)字、最大的字符串和最近的日期時間count（distinct *）任意基于行的數(shù)據(jù)類型統(tǒng)計結(jié)果集中全部記錄行的數(shù)量，最多可達214748364

2、7行count_big（distinct *）任意基于行的數(shù)據(jù)類型類似于count()函數(shù)，但因其返回值使用了bigint數(shù)據(jù)類型，所以最多可以統(tǒng)計263-1行1SUM()函數(shù)和AVG()函數(shù)兩個函數(shù)都是對列式數(shù)字型的進行計算，只不過SUM()是對列求和；而AVG()是對列求平均值。示例：求“grade”表中每學期的課程成績的總和。在查詢分析器中輸入的SQL語句如下：use studentselect sum(課程成績) as 總成績 from grade 實現(xiàn)的過程如圖1所示。圖1 求課程成績的總和當與GROUP BY子句一起使用時，每個聚集函數(shù)都為每一組生成一個值，而不是對整個表生成一個值

3、。示例：在“student”表中，按“性別”分別求年齡的平均值。在查詢分析器中輸入的SQL語句如下：use studentselect 性別 ,avg(年齡) as 平均年齡 from student group by 性別 實現(xiàn)的過程如圖2所示。圖2 男女生的平均年齡2MIN()函數(shù)和MAX()函數(shù)MIN()和MAX()函數(shù)分別查詢列中的最小值和最大值。但列的數(shù)據(jù)包含數(shù)字、字符或日期/時間信息。MIN()和MAX()函數(shù)結(jié)果與列中數(shù)據(jù)的數(shù)據(jù)類型完全相同。示例：查詢“student”表中最早出生的學生。在查詢分析器中輸入的SQL語句如下：use studentselect min(出生日期)

4、as 最早出生 from Student實現(xiàn)的過程結(jié)果如圖3所示。圖3 學生表中年齡最小的學生信息下面把GROUP BY子句和MAX()函數(shù)結(jié)合使用。示例：在“student”表中，按“性別”分別求年齡的最大值。在查詢分析器中輸入的SQL語句如下：use studentselect 性別, max(年齡) as 最大年齡 from Student group by 性別 實現(xiàn)的過程如圖4所示。圖4 男女生中年齡的最大值3COUNT()函數(shù)和COUNT_big()函數(shù)COUNT()函數(shù)和COUNT_big()函數(shù)兩個函數(shù)都是對列中數(shù)據(jù)值的數(shù)目進行計數(shù)。它們返回的值總是一個整數(shù)，不管列的數(shù)據(jù)類型。

5、示例：求“student”表中女生的人數(shù)。在查詢分析器中輸入的SQL語句如下：use studentselect count(年齡) as 女生記錄總數(shù) from student where 性別=女實現(xiàn)的過程如圖5所示。圖5 “Student”表中女生的記錄總數(shù)COUNT（*）就可以求整個表所有的記錄數(shù)。例如，求“student”表中所有的記錄數(shù)，SQL語句如下：use studentselect count(*) from student 4消除重復記錄（DISTINCT）指定DISTINCT關鍵字不但可以消除查詢結(jié)果中的重復記錄，而且在使用SUM()、AVG()和COUNT()聚合函數(shù)時

6、，可以從列中消除重復的值。DISTINCT關鍵字和聚合函數(shù)使用的格式是：聚合函數(shù)名稱（DISTINCT 列名）。示例：在“grade”表中，統(tǒng)計多少學生參加考試。在查詢分析器中輸入的SQL語句如下：use studentselect count(學號) from grade 實現(xiàn)的過程如圖6所示。圖6 統(tǒng)計參加考試的學生從上面的統(tǒng)計結(jié)果不難看出，實際上參加考試的學生是學號從B001B005共5名，其中有重復的學號。這樣為了正確統(tǒng)計到底有多少學生參加考試，就必須用到關鍵字DISTINCT。示例：在“grade”表中，統(tǒng)計多少學生參加考試。在查詢分析器中輸入的SQL語句如下：use student

7、select count(distinct 學號) from grade 實現(xiàn)的過程如圖7所示。圖7 使用DISTINCT關鍵字統(tǒng)計參加考試的學生注意：當使用DISTINCT關鍵字時，聚合函數(shù)的參數(shù)必須是一個簡單的列名。篩選分組結(jié)果用GROUP BY可以實現(xiàn)數(shù)據(jù)分組操作，但有時用戶不需要對數(shù)據(jù)表中所有的數(shù)據(jù)進行分組，這時就需要使用HAVING子句來篩選分組。示例：在“grade”表中，查詢參加同一門課程考試的同學至少兩個人的課程成績總和。在查詢分析器中輸入的SQL語句如下：use studentselect 課程代號, sum(課程成績) as 課程總成績 from grade group b

8、y 課程代號 having count(*)=2 實現(xiàn)的過程如圖1所示。圖1 至少兩個人對加同一門考試的課程成績總和1SQL SELECT語句的執(zhí)行順序下面給出SQL SELECT語句的執(zhí)行順序。（1）FROM子句組裝來自不同數(shù)據(jù)源的數(shù)據(jù)。（2）WHERE子句基于指定的條件對記錄行進行篩選。（3）GROUP BY子句將數(shù)據(jù)劃分為多個分組。（4）使用聚集函數(shù)進行計算。（5）使用HAVING子句篩選分組。（6）計算所有的表達式。（7）使用ORDER BY對結(jié)果集進行排序。示例：在“grade”表中，把“學號”內(nèi)容不為空的記錄按照“學號”分組，并且篩選分組結(jié)果，選出“課程成績”大于92的學生信息。在

9、查詢分析器中輸入的SQL語句如下：use studentselect 學號,avg(課程成績) as 平均成績from gradewhere 學號 is not nullgroup by 學號having avg(課程成績)92order by 平均成績實現(xiàn)的過程如圖2所示。圖2 查詢統(tǒng)計“student”表下面給出上個示例中SQL語句的執(zhí)行順序。（1）首先執(zhí)行FROM子句，從“grade”表組裝數(shù)據(jù)源的數(shù)據(jù)。（2）執(zhí)行WHERE子句，篩選“grade”表中所有數(shù)據(jù)不為NULL的數(shù)據(jù)。（3）執(zhí)行GROUP BY子句，把“grade”表按“學號”列進行分組。（4）計算AVG()聚集函數(shù)，按“課程

10、成績”求出平均成績的具體數(shù)值。（5）執(zhí)行HAVING子句，篩選課程的平均成績大于92分的學生信息。（6）執(zhí)行ORDER BY子句，把最后的結(jié)果按“平均成績”進行排序。2HAVING子句在分組搜索條件上的限制HAVING子句指定的搜索條件必須是作為一個整體應用于組而不是應用于各個記錄。所以HAVING的搜索條件是有限制的，列舉如下：l l 一個常量。l l 一個聚合函數(shù)，這個聚合函數(shù)生成一個值，該值匯總組中的記錄。l l 一個分組列，按照定義，這個分組字段在這個組的每一記錄中有同樣的值。l l 一個包含上述各項組合的表達式。示例：在“grade”表中，按“學期”分組，求“學期”值不為空的課程成績

11、平均值。在查詢分析器中輸入的SQL語句如下：use studentselect avg(課程成績) as 平均成績 from grade group by 學期 having 學期 is not null實現(xiàn)的過程如圖3所示。 圖3 求“grade”表中按“學期”分組的平均成績示例：在“grade”表中，按“課程類別”分組，并且查詢“課程類別”不是“計算機類”的課程信息。在查詢分析器中輸入的SQL語句如下：use studentselect 課程類別from coursegroup by 課程類別having 課程類別計算機類實現(xiàn)的過程如圖4所示。圖4 “grade”表按“課程類別”分組統(tǒng)計3

12、比較HAVING子句與WHERE子句兩個子句的相似之處。（1）它們都是從結(jié)果表中篩選數(shù)據(jù)。（2）它們都設置了某些數(shù)據(jù)能通過而其他數(shù)據(jù)不能通過的條件。兩個子句的不同之處。（1）WHERE子句可以在進行任何處理之前從原表、原始數(shù)據(jù)中篩選行。（2）HAVING子句可以在進行絕大部分處理之后篩選已分組和已總結(jié)的數(shù)據(jù)。（3）WHERE子句不能在它設置的條件之中使用列函數(shù)。（4）HAVING子句可以在它的條件中使用列函數(shù)。理解HVING子句的最好方法就是記住SELECT語句中的哪些子句是按照明確的次序進行處理的。WHERE子句只能接收來自FROM子句的輸入，而HAVING子句則可以接收來自GROUP BY

13、、WHERE子句或FROM子句的輸入。這是一個微妙但卻重要的差別。示例：在“grade”表中，把“課程成績”大于92分的按“學期”分組求平均成績。在查詢分析器中輸入的SQL語句如下：use studentselect 學期,avg(課程成績) as 平均成績from gradewhere 課程成績92group by 學期實現(xiàn)的過程如圖5所示。圖5 按學期求大于92分的課程的平均成績上個例子，首先挑選出“課程成績”大于92分的學生信息，然后按“學期”再分組求課程成績的平均值。下面把WHERE子句替換成HAVING子句，在查詢分析器中運行的結(jié)果如圖6所示。圖6 按“學期”分組用HAVING子句設

14、置條件SQL語句如下所示：use studentselect 學期,avg(課程成績) as 平均成績from gradegroup by 學期having 課程成績92 由此可見，執(zhí)行用HAVING子句替換WHERE子句的語句是錯誤的。因為“課程成績”列既不包含在聚合函數(shù)中，也不包含在GROUP BY子句中。下面改變HAVING子句的條件，這個子句包括一個用了聚合函數(shù)的列。示例：在“grade”表中，按“學期”求課程成績的平均值，并篩選出平均成績大于92分的。在查詢分析器中輸入的SQL語句如下：use studentselect 學期,avg(課程成績) as 平均成績from gradeg

15、roup by 學期having avg(課程成績)92 實現(xiàn)的過程如圖7所示。圖7 按“學期”求成績的平均值并用HAVING進行篩選當按“學期”分完組后，HAVING子句就應用于這些結(jié)果。對于每一個組來說，都要求成績的平均值，但只有平均成績大于92分才能包括在結(jié)果中。HAVING對分組后的數(shù)據(jù)可以進行篩選，并且可以使用AVG（）或SUM（）之類的設置功能，而這些是在WHERE子句中無法使用的。4使用ALL關鍵字在GROUP BY子句中使用ALL關鍵字。只有在SQL語句還包括WHERE子句時，ALL關鍵字才有意義。如果使用ALL關鍵字，那么查詢結(jié)果將包括由 GROUP BY子句產(chǎn)生的所有組，即

16、使某些組沒有符合查詢條件的行。沒有ALL關鍵字，包含GROUP BY子句的SELECT語句將不顯示沒有符合條件的行的組。示例：在“grade”表中，按“課程代號”分組求出課程的平均成績，并不顯示“課程成績”的值為NULL值的行。在查詢分析器中輸入的SQL語句如下：use studentselect 課程代號,avg(課程成績) as 平均成績from grade where 學號!=b003group by 課程代號 having 課程代號 is not null實現(xiàn)的過程如圖8所示。圖8 分組查詢成績表在上面的例子中使用ALL關鍵字。示例：在“grade”表中，按“課程代號”分組求出課程的平

17、均成績，并不顯示“課程成績”的值為NULL值。在查詢分析器中輸入的SQL語句如下：use studentselect 課程代號,avg(課程成績) as 平均成績from grade where 學號!=b003group by all 課程代號 having 課程代號 is not null實現(xiàn)的過程如圖9所示。 圖9 使用ALL關鍵字分組查詢成績表5在分組查詢中使用CUBE運算符CUBE運算符的主要作用是自動對GROUP BY子句中列出的字段進行分組匯總運算。CUBE運算符生成的結(jié)果集是多維數(shù)據(jù)集。多維數(shù)據(jù)集是事實數(shù)據(jù)的擴展，事實數(shù)據(jù)即記錄個別事件的數(shù)據(jù)。擴展建立在用戶打算分析的列上，這些

18、列被稱為維。多維數(shù)據(jù)集是一個結(jié)果集，其中包含了各維度的所有可能組合的交叉表格。CUBE運算符在SQL語句的GROUP BY子句中指定。該語句的選擇列表應包含維度列和聚合函數(shù)表達式。GROUP BY應指定維度列和關鍵字WITH CUBE。結(jié)果集將包含維度列中各值的所有可能組合，以及與這些維度值組合相匹配的基礎行中的聚集值。下面舉一個簡單的例子，一個簡單的表Inventory，其內(nèi)容如表1所示：表1 Inventory表結(jié)構(gòu)ItemColorQuantityTableBlue124TableRed223ChairBlue101ChairRed210在查詢分析器中輸入的SQL語句如下： SELECT

19、 Item, Color, SUM(Quantity) AS QtySumFROM InventoryGROUP BY Item, Color WITH CUBE其查詢結(jié)果如表2所示：表2 查詢結(jié)果集ItemColorQtySumChairBlue101.00ChairRed210.00ChairNULL311.00TableBlue124.00TableRed223.00TableNULL347.00NULLNULL658.00NULLBlue225.00NULLRed433.00下面著重考查下列各行,如表3、表4、表5和表6所示。表3 記錄行1ChairNULL311.00這一行顯示了It

20、em維度中值為Chair的所有行的小計。對Color維度返回了NULL值，表示該行所顯示的聚集包括Color維度為任意值的行。表4 記錄行2TableNULL347.00這一行類似，但顯示的是Item維度中值為Table的所有行的小計。表5 記錄行3NULLNULL658.00這一行顯示了多維數(shù)據(jù)集的總計。Item和Color維度的值都是NULL，表示兩個維度中的所有值都匯總在該行中。表6 記錄行4NULLBlue225.00NULLRed433.00這兩行顯示了Color維度的小計。兩行中的Item維度值都是NULL，表示聚集數(shù)據(jù)來自Item維度為任意值的行。示例：在“grade”表中，按“

21、學期”和“課程代號”分組求課程的平均成績，并且用CUBE運算符進行小計。在查詢分析器中輸入的SQL語句如下：use studentselect 學期, 課程代號,avg(課程成績) as 平均成績from grade group by 學期,課程代號 with cube having 課程代號 is not null 實現(xiàn)的過程如圖10所示。圖10 對“grade”表統(tǒng)計小計6在分組查詢中使用ROLLUP在使用GROUP BY生成包含小計和合計的報表時，ROLLUP 運算符很有用。ROLLUP運算符生成的結(jié)果集類似于CUBE運算符所生成的結(jié)果集。CUBE和ROLLUP之間的區(qū)別在于：（1）CU

22、BE生成的結(jié)果集顯示了所選列中值的所有組合的聚集。（2）ROLLUP生成的結(jié)果集顯示了所選列中值的某一層次結(jié)構(gòu)的聚集。下面同樣以一個簡單表Inventory為例來介紹如何使用ROLLUP運算符，如表7所示。表7 Inventory表ItemColorQuantityTableBlue124TableRed223ChairBlue101ChairRed210在查詢分析器中輸入的SQL語句如下：SELECT CASE WHEN (GROUPING(Item) = 1) THEN ALL ELSE ISNULL(Item, UNKNOWN) END AS Item, CASE WHEN (GROUP

23、ING(Color) = 1) THEN ALL ELSE ISNULL(Color, UNKNOWN) END AS Color, SUM(Quantity) AS QtySumFROM InventoryGROUP BY Item, Color WITH ROLLUP其執(zhí)行結(jié)果集如表7所示。如果查詢中的ROLLUP 關鍵字更改為CUBE，那么CUBE結(jié)果集與上述結(jié)果相同，只是在結(jié)果集的末尾還會返回下列兩行，如表8所示。表8 執(zhí)行結(jié)果集1ItemColorQtySumChairBlue101.00ChairRed210.00ChairALL311.00TableBlue124.00TableRed223.00Ta