SQL Server溫故系列(5)：SQL 查詢之分組查詢 GROUP BY

来源:https://www.cnblogs.com/hanzongze/archive/2019/07/10/tsql-groupby.html
-Advertisement-
Play Games

《SQL Server溫故系列》之分組查詢 GROUP BY。GROUP BY 是一種能將查詢結果劃分為多個行組的查詢語句的子句，其目的通常是為了在每個組上執行一個或多個聚合運算，所以 GROUP BY 通常會與聚合函數一塊兒出現在查詢語句中。本文主要講述了 SQL Server 中 GROUP B... ...


1、GROUP BY 與聚合函數

GROUP BY 是一種能將查詢結果劃分為多個行組的查詢語句的子句，其目的通常是為了在每個組上執行一個或多個聚合運算，所以 GROUP BY 通常會與聚合函數一塊兒出現在查詢語句中。

GROUP BY 的標準分組方式是按所有分組欄位的值依次來分組。假如欄位 A 的值有 3 種，欄位 B 的值有 2 種；如果是GROUP BY A，那麼就會被分為 3 組；而如果是GROUP BY A,B，那麼就會先被 A 分為 3 組，然後這 3 組又會被 B 再各自分為 2 組，最終會被分為 3×2 等於 6 組。

顯然，GROUP BY B,A最終也會被分為 6 組，換而言之，標準分組時的欄位的順序不會對分組結果產生影響。但分組欄位的順序會影響查詢結果的排序，如果想要改變結果集的排序，可以通過 ORDER BY 子句來實現。

示例一、查詢統計學生 1、2、3 的第 1 次考試成績，且按各科總分降序排列：

SELECT t.StudentId,COUNT(1) 科目數,
    SUM(t.Scores) 總分,MAX(t.Scores) 最高分,MIN(t.Scores) 最低分,AVG(t.Scores) 平均分 
FROM T_ExamResults t 
WHERE t.Counts = 1 AND t.StudentId IN(1,2,3) 
GROUP BY t.StudentId 
ORDER BY 總分 DESC;

示例二、查詢統計學生 1、2、3 的第 1 次考試成績，且按班級名稱和學生名稱來升序排列：

SELECT t1.Code,t1.Name,t3.Name,COUNT(1) 科目數,
    SUM(t2.Scores) 總分,MAX(t2.Scores) 最高分,MIN(t2.Scores) 最低分,AVG(t2.Scores) 平均分 
FROM T_Students t1 
JOIN T_ExamResults t2 ON t1.Id = t2.StudentId AND t2.Counts = 1 
JOIN T_Classes t3 ON t1.ClassId = t3.Id 
WHERE t1.Id IN(1,2,3) 
GROUP BY t1.Code,t1.Name,t3.Name 
ORDER BY t3.Name,t1.Name DESC;

註意：在含有 GROUP BY 子句的查詢語句中，每組只會返回一行數據，且查詢選擇列表中的列只能是 GROUP BY 中的欄位或聚合函數表達式。

2、GROUP BY 與 HAVING

HAVING 子句的作用有點類似於 WHERE 子句，說到底它們都是過濾數據用的，但不同的是，WHERE 子句過濾的最小單位是數據行，而 HAVING 子句過濾的最小單位是行組。相較於 WHERE 子句，HAVING 子句最大的優勢就是支持聚合函數。

HAVING 子句只能在查詢語句中使用，且通常與 GROUP BY 子句一起使用。如果查詢語句中沒有 GROUP BY 子句，那麼就會有隱式的單一行組，但這通常是沒有意義的。例如要查詢統計平均分達到 80 分的學生第 1 次考試成績，且按總分倒序排列，示例如下：

SELECT t.StudentId,COUNT(1) 科目數,
    SUM(t.Scores) 總分,MAX(t.Scores) 最高分,MIN(t.Scores) 最低分,AVG(t.Scores) 平均分 
FROM T_ExamResults t 
WHERE t.Counts = 1 
GROUP BY t.StudentId 
HAVING AVG(t.Scores) >= 80
ORDER BY SUM(t.Scores) DESC;

3、GROUP BY 擴展分組

在實際的開發工作中，尤其是開發數據報表，往往需要統計多維度的小計和合計。大多數情況下用 UNION 也能達到類似效果，但實現起來比較繁瑣，靈活性較差，性能往往也比較低。針對這類需求，SQL Server 提供了幾個實用的擴展分組，以便能更好的實現這些需求。

3.1、GROUP BY ROLLUP

ROLLUP 是對 GROUP BY 子句的一種擴展，它允許計算標準分組及部分維度的小計及合計。ROLLUP 的計算結果與分組欄位的順序有關，因為它的分組過程具有方向性，先計算標準分組，然後從右到左遞減計算更高一級的小計，直到所有欄位被計算完，最後計算合計。

對於GROUP BY ROLLUP(a,b,c)，結果具有 (a,b,c)、(a,b,NULL)、(a,NULL,NULL)、(NULL,NULL,NULL) 唯一值的組。換而言之，GROUP BY ROLLUP(a,b,c)的結果集就等價於GROUP BY a,b,c的結果集，加上GROUP BY a,b的結果集，再加上GROUP BY a的結果集，最後加上不帶GROUP BY的總計結果集。

示例一、查詢統計 1、2、3 班的學生個數及年齡：

WITH t AS(
    SELECT t.Code,t.Name,DATEDIFF(YEAR,t.Birthday,GETDATE()) Age,t.ClassId 
    FROM T_Students t 
    WHERE t.ClassId IN(1,2,3)
)
SELECT t.ClassId,COUNT(1) 學生個數,MAX(t.Age) 最大年齡,MIN(t.Age) 最小年齡 
FROM t 
GROUP BY ROLLUP(t.ClassId);

查詢結果如下：

ClassId     學生個數        最大年齡        最小年齡
----------- ----------- ----------- -----------
1           10          20          15
2           9           20          16
3           9           21          15
NULL        28          21          15

示例二、查詢統計 1、2 班的學生個數及年齡：

WITH t AS(
    SELECT t.Code,t.Name,DATEDIFF(YEAR,t.Birthday,GETDATE()) Age,t.ClassId,t.Gender 
    FROM T_Students t 
    WHERE t.ClassId IN(1,2)
)
SELECT t.ClassId,t.Gender,COUNT(1) 學生個數,MAX(t.Age) 最大年齡,MIN(t.Age) 最小年齡 
FROM t 
GROUP BY ROLLUP(t.ClassId,t.Gender);

查詢結果如下：

ClassId     Gender      學生個數        最大年齡        最小年齡
----------- ----------- ----------- ----------- -----------
1           0           6           19          15
1           1           4           20          18
1           NULL        10          20          15
2           0           4           20          17
2           1           5           20          16
2           NULL        9           20          16
NULL        NULL        19          20          15

示例三、查詢統計 1、2 班的學生個數及年齡：

WITH t AS(
    SELECT t.Code,t.Name,DATEDIFF(YEAR,t.Birthday,GETDATE()) Age,t.ClassId,t.Gender 
    FROM T_Students t 
    WHERE t.ClassId IN(1,2)
)
SELECT t.ClassId,t.Gender,COUNT(1) 學生個數,MAX(t.Age) 最大年齡,MIN(t.Age) 最小年齡 
FROM t 
GROUP BY t.ClassId,ROLLUP(t.Gender);

查詢結果如下：

ClassId     Gender      學生個數        最大年齡        最小年齡
----------- ----------- ----------- ----------- -----------
1           0           6           19          15
1           1           4           20          18
1           NULL        10          20          15
2           0           4           20          17
2           1           5           20          16
2           NULL        9           20          16

3.2、GROUP BY CUBE

CUBE 是對 GROUP BY 子句的一種擴展，它允許計算標準分組及所有維度的小計及合計。CUBE 會對所有可能的分組進行統計，從而生成交叉報表。CUBE 比 ROLLUP 的分組更多，完全包含了 ROLLUP 的統計結果，且計算結果與分組欄位的順序無關，但如果欄位順序不同，預設的結果集排序會有不同。

對於GROUP BY CUBE(a,b)，結果具有 (a,b)、(a,NULL)、(NULL,b)、(NULL,NULL) 唯一值的組。換而言之，GROUP BY CUBE(a,b)的結果集就等價於GROUP BY a,b的結果集，加上GROUP BY a的結果集，再加上GROUP BY b的結果集，最後加上不帶GROUP BY的總計結果集。

示例一、查詢統計 1、2、3 班的學生個數及年齡：

WITH t AS(
    SELECT t.Code,t.Name,DATEDIFF(YEAR,t.Birthday,GETDATE()) Age,t.ClassId 
    FROM T_Students t 
    WHERE t.ClassId IN(1,2,3)
)
SELECT t.ClassId,COUNT(1) 學生個數,MAX(t.Age) 最大年齡,MIN(t.Age) 最小年齡 
FROM t 
GROUP BY CUBE(t.ClassId);

查詢結果如下：

ClassId     學生個數        最大年齡        最小年齡
----------- ----------- ----------- -----------
1           10          20          15
2           9           20          16
3           9           21          15
NULL        28          21          15

示例二、查詢統計 1、2 班的學生個數及年齡：

WITH t AS(
    SELECT t.Code,t.Name,DATEDIFF(YEAR,t.Birthday,GETDATE()) Age,t.ClassId,t.Gender 
    FROM T_Students t 
    WHERE t.ClassId IN(1,2)
)
SELECT t.ClassId,t.Gender,COUNT(1) 學生個數,MAX(t.Age) 最大年齡,MIN(t.Age) 最小年齡 
FROM t 
GROUP BY CUBE(t.ClassId,t.Gender);

查詢結果如下：

ClassId     Gender      學生個數        最大年齡        最小年齡
----------- ----------- ----------- ----------- -----------
1           0           6           19          15
2           0           4           20          17
NULL        0           10          20          15
1           1           4           20          18
2           1           5           20          16
NULL        1           9           20          16
NULL        NULL        19          20          15
1           NULL        10          20          15
2           NULL        9           20          16

示例三、查詢統計 1、2 班的學生個數及年齡：

WITH t AS(
    SELECT t.Code,t.Name,DATEDIFF(YEAR,t.Birthday,GETDATE()) Age,t.ClassId,t.Gender 
    FROM T_Students t 
    WHERE t.ClassId IN(1,2)
)
SELECT t.ClassId,t.Gender,COUNT(1) 學生個數,MAX(t.Age) 最大年齡,MIN(t.Age) 最小年齡 
FROM t 
GROUP BY t.ClassId,CUBE(t.Gender);

查詢結果如下：

ClassId     Gender      學生個數        最大年齡        最小年齡
----------- ----------- ----------- ----------- -----------
1           0           6           19          15
1           1           4           20          18
1           NULL        10          20          15
2           0           4           20          17
2           1           5           20          16
2           NULL        9           20          16

3.3、GROUP BY GROUPING SETS

GROUPING SETS 是對 GROUP BY 子句的一種擴展，它允許一次計算多個標準分組的小計。GROUPING SETS 的功能相當於將多個 GROUP BY 子句組合到一個 GROUP BY 子句中，類似於用 UNION ALL 合併多個 GROUP BY 的結果集，所以它的計算結果與排序欄位的順序無關，而且不會合併重覆組。

例如GROUP BY GROUPING SETS(ROLLUP(A))和GROUP BY ROLLUP(A)的結果集相同，GROUP BY GROUPING SETS(A,B)和GROUP BY A加GROUP BY B的結果集相同。示例如下：

WITH t AS(
    SELECT t.Code,t.Name,DATEDIFF(YEAR,t.Birthday,GETDATE()) Age,t.ClassId,t.Gender 
    FROM T_Students t 
    WHERE t.ClassId IN(1,2)
)
SELECT t.ClassId,COUNT(1) 學生個數,MAX(t.Age) 最大年齡,MIN(t.Age) 最小年齡 
FROM t 
GROUP BY GROUPING SETS(ROLLUP(t.ClassId),CUBE(t.ClassId));

查詢結果如下：

ClassId     學生個數        最大年齡        最小年齡
----------- ----------- ----------- -----------
1           10          20          15
2           9           20          16
NULL        19          20          15
1           10          20          15
2           9           20          16
NULL        19          20          15

GROUPING SETS 中還支持GROUP BY ()，用於指定生成總計的空組。例如要查詢統計浙江地區各級別行政區個數，及總計個數，示例如下：

SELECT t.Level 級別,COUNT(1) 個數 
FROM T_Districts t 
WHERE SUBSTRING(t.Code,1,2) = '33' 
GROUP BY GROUPING SETS(t.Level,());

查詢結果如下：

級別          個數
----------- -----------
1           1
2           6
3           10
NULL        17

4、GROUP BY 擴展函數

4.1、GROUPING 函數

GROUPING 函數用於指示當前行是否為聚合行，如果它返回 1 則表示聚合，相反，返回 0 則表示未聚合。僅當指定了 GROUP BY 時，GROUPING 才能在 SELECT 子句、HAVING 或 ORDER BY 子句中使用。

通常將一個分組欄位作為該函數的參數，然後通過判斷它的返回值來區分聚集行與常規行，從而進一步對結果集美化或過濾。示例如下：

SELECT GROUPING(t.Level) 標誌,t.Level 級別,COUNT(1) 個數 
FROM T_Districts t 
WHERE SUBSTRING(t.Code,1,2) = '33' 
GROUP BY GROUPING SETS(t.Level,());

查詢結果如下：

標誌   級別          個數
---- ----------- -----------
0    1           1
0    2           6
0    3           10
1    NULL        17

4.2、GROUPING_ID 函數

GROUPING 函數用於計算分組級別，它將返回與行相關聯的 GROUPING 位向量對應的數值。GROUPING_ID 按從左到右的順序計算，如果是分組欄位，則為 0，如果是小計或合計則為 1，然後按欄位的順序將計算結果組成二進位序列（位向量），最後將位向量轉化為十進位數。僅當指定了 GROUP BY 時，GROUPING 才能在 SELECT 子句、HAVING 或 ORDER BY 子句中使用。

GROUPING_ID 函數在功能上等效於多個 GROUPING 函數，當查詢結果有多個聚合級別時，使用該函數會更容易表達行過濾條件。示例如下：

SELECT t.CourseId,t.StudentId,t.Counts,MAX(t.Scores) Scores,
    GROUPING_ID(t.CourseId) gc,GROUPING_ID(t.StudentId) gs,GROUPING_ID(t.Counts) gt,
    GROUPING_ID(t.CourseId,t.StudentId) gcs,
    GROUPING_ID(t.StudentId,t.Counts) gst,
    GROUPING_ID(t.CourseId,t.Counts) gct,
    GROUPING_ID(t.CourseId,t.StudentId,t.Counts) gcst 
FROM T_ExamResults t 
WHERE t.StudentId = 1 AND t.Counts = 2 
GROUP BY ROLLUP(t.CourseId,t.StudentId,t.Counts);

查詢結果如下：

CourseId   StudentId  Counts    Scores    gc      gs      gt      gcs     gst     gct     gcst
---------- ---------- --------- --------- ------- ------- ------- ------- ------- ------- -------
1          1          2         63.0      0       0       0       0       0       0       0
1          1          NULL      63.0      0       0       1       0       1       1       1
1          NULL       NULL      63.0      0       1       1       1       3       1       3
2          1          2         98.0      0       0       0       0       0       0       0
2          1          NULL      98.0      0       0       1       0       1       1       1
2          NULL       NULL      98.0      0       1       1       1       3       1       3
3          1          2         73.0      0       0       0       0       0       0       0
3          1          NULL      73.0      0       0       1       0       1       1       1
3          NULL       NULL      73.0      0       1       1       1       3       1       3
NULL       NULL       NULL      98.0      1       1       1       3       3       3       7

5、本文小結

本文主要講述了 SQL Server 中分組查詢的相關知識點，包括 GROUP BY 與聚合函數、HAVING 聯合使用及 GROUP BY 的標準分組、擴展分組、擴展函數等。

另外，不知道會不會有讀者感到疑惑，為什麼擴展分組返回的結果集中經常會出現 NULL 值？其實這是 NULL 的一個特殊應用，它在 ROLLUP、CUBE 或 GROUPING SETS 操作的結果集內作為欄位的占位符，表示全體（數據）。

本文參考鏈接：

去導航目錄篇下載創建本系列博文通用庫表及數據的 SQL 語句

本文鏈接：http://www.cnblogs.com/hanzongze/p/tsql-groupby.html
版權聲明：本文為博客園博主 韓宗澤 原創，作者保留署名權！歡迎通過轉載、演繹或其它傳播方式來使用本文，但必須在明顯位置給出作者署名和本文鏈接！個人博客，能力有限，若有不當之處，敬請批評指正，謝謝！


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • ubuntu14徹底刪除mysql！！！（精）
    解決方法: 刪除mysql前 先刪除一下 /var/lib/mysql 還有 /etc/mysql sudo rm /var/lib/mysql/ -R sudo rm /etc/mysql/ -R sudo apt-get autoremove mysql* --purge sudo apt-ge ...
  • 壓縮打包
    [toc] 壓縮打包 壓縮打包介紹 ​ windows .rar Linux`有自己所特有的壓縮工具。 如果希望windows和Linux互相能使用的壓縮工具, 建議 格式 壓縮的好處主要有： 節省磁碟空間占用率 節省網路傳輸帶寬消耗 網路傳輸更加快捷 類型 | 格式 | 壓縮工具 | | | | ...
  • Linux系統：centos7下搭建ElasticSearch中間件，常用介面演示
    一、中間件簡介 1、基礎概念 ElasticSearch是一個基於Lucene的搜索伺服器。它提供了一個分散式多用戶能力的全文搜索引擎，基於RESTful web介面。Elasticsearch是用Java開發的，並作為Apache許可條款下的開放源碼發佈，是當前流行的企業級搜索引擎。 2、分散式數 ...
  • Deepin15.10:配置OracleJDK(SunJDK)環境變數
    一、在Oracle官網下載尾碼為：tar.gz的linux版本JDK。 二、解壓縮到：/opt/JDK目錄下。 三.通過終端執行：sudo vim /etc/profile 執行後按“i”進行編輯，在最後添加。 四、按ESC接Shift+zz保存退出。 五、執行： 安裝成功了！ ...
  • 談軟體相容性與系統平臺
    軟體相容性的定義及操作系統對比： 向上（下）相容指的是按某檔機器編製的軟體，不加修改就能運行於比它高（低）檔的機器上。 向前（後）相容指的是按某個時期投入市場的該型號機器上編製的軟體，不加修改就能運行於比它前（後）投入市場的機器上。 目前市面上流行的操作系統有：windows、linux、mac、a ...
  • QT無視窗狀態下對鍵盤事件的監聽
    Question：最近在搞linux下的一個客戶端項目，需要接收鍵盤事件，但是又不能有界面，這種情況怎麼處理呢? 1、首先需要在main方法中註冊，使用installEventFilter方法把這個類的指針傳進去 2、在Test類中重寫eventFilter方法，這樣就可以進行監聽了 3、在even ...
  • Linux下網路設置
    1、臨時IP配置 # ifconfig eth0 192.168.110.118 netmask 255.255.255.0 gateway 192.168.110.2 up # ifconfig eth0 192.168.110.118/24 up # ifconfig eth0:1 192.16 ...
  • ansible碎碎念
    1. Using a SSH password instead of a key is not possible because Host Key checking is enabled and sshpass does not support this. Please add this host' ...
一周排行
    -Advertisement-
    Play Games
  • C# + WPF 音頻播放器 界面優雅，體驗良好
    前言 本文介紹一款使用 C# 與 WPF 開發的音頻播放器，其界面簡潔大方，操作體驗流暢。該播放器支持多種音頻格式（如 MP4、WMA、OGG、FLAC 等），並具備標記、實時歌詞顯示等功能。 另外，還支持換膚及多語言（中英文）切換。核心音頻處理採用 FFmpeg 組件，獲得了廣泛認可，目前 Git ...
  • OAuth2.0授權-gitee授權碼模式
    OAuth2.0授權驗證-gitee授權碼模式 本文主要介紹如何筆者自己是如何使用gitee提供的OAuth2.0協議完成授權驗證並登錄到自己的系統，完整模式如圖 1、創建應用 打開gitee個人中心->第三方應用->創建應用 創建應用後在我的應用界面，查看已創建應用的Client ID和Clien ...
  • FastReport.OpenSource .Net下開源免費報表列印組件
    解決了這個問題：《winForm下，fastReport.net 從.net framework 升級到.net5遇到的錯誤“Operation is not supported on this platform.”》 本文內容轉載自：https://www.fcnsoft.com/Home/Sho ...
  • 如何在 ASP.NET Core Web API 方法執行前後 “偷偷“ 作一些 “壞“ 事？初識 ActionFilterAttribute
    國內文章 WPF 從裸 Win 32 的 WM_Pointer 消息獲取觸摸點繪製筆跡 https://www.cnblogs.com/lindexi/p/18390983 本文將告訴大家如何在 WPF 裡面，接收裸 Win 32 的 WM_Pointer 消息，從消息裡面獲取觸摸點信息，使用觸摸點 ...
  • 在 ASP.NET Core Web API 中使用異常篩選器捕獲和統一處理異常
    前言 給大家推薦一個專為新零售快消行業打造了一套高效的進銷存管理系統。 系統不僅具備強大的庫存管理功能，還集成了高性能的輕量級 POS 解決方案，確保頁面載入速度極快，提供良好的用戶體驗。 項目介紹 Dorisoy.POS 是一款基於 .NET 7 和 Angular 4 開發的新零售快消進銷存管理 ...
  • AbpHelper CLI積累與實戰分享
    ABP CLI常用的代碼分享 一、確保環境配置正確 安裝.NET CLI： ABP CLI是基於.NET Core或.NET 5/6/7等更高版本構建的，因此首先需要在你的開發環境中安裝.NET CLI。這可以通過訪問Microsoft官網下載並安裝相應版本的.NET SDK來實現。 安裝ABP ...
  • 記一次HttpClient使用問題分析
    問題 問題是這樣的：第三方的webapi，需要先調用登陸介面獲取Cookie，訪問其它介面時攜帶Cookie信息。 但使用HttpClient類調用登陸介面，返回的Headers中沒有找到Cookie信息。 分析 首先，使用Postman測試該登陸介面，正常返回Cookie信息，說明是HttpCli ...
  • .NET周刊【9月第3期 2024-09-15】
    國內文章 關於.NET在中國為什麼工資低的分析 https://www.cnblogs.com/thinkingmore/p/18406244 .NET在中國開發者的薪資偏低，主要因市場需求、技術棧選擇和企業文化等因素所致。歷史上，.NET曾因微軟的閉源策略發展受限，儘管後來推出了跨平臺的.NET ...
  • 我的網站集成ElasticSearch初體驗
    在WPF開發應用中，動畫不僅可以引起用戶的註意與興趣，而且還使軟體更加便於使用。前面幾篇文章講解了畫筆（Brush），形狀（Shape），幾何圖形（Geometry），變換（Transform）等相關內容，今天繼續講解動畫相關內容和知識點，僅供學習分享使用，如有不足之處，還請指正。 ...
  • 第23篇 委托的概述
    什麼是委托？ 委托可以說是把一個方法代入另一個方法執行，相當於指向函數的指針；事件就相當於保存委托的數組； 1.實例化委托的方式： 方式1：通過new創建實例： public delegate void ShowDelegate(); 或者 public delegate string ShowDe ...
所有分類