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
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類