如何使用 SQL GROUP BY 分組和排序數據

来源:https://www.cnblogs.com/vin-c/archive/2022/05/16/16275822.html
-Advertisement-
Play Games

本文介紹如何使用 SQL GROUP BY 子句分組數據，以便彙總表內容的子集。這涉及兩個新 SELECT 語句子句：GROUP BY 子句和 HAVING 子句。 一、數據分組 從 如何使用 SQL AVG、COUNT、MAX、MIN 和 SUM 彙總數據 中得知，使用 SQL 聚集函數可以彙總數 ...


目錄

本文介紹如何使用 SQL GROUP BY 子句分組數據，以便彙總表內容的子集。這涉及兩個新 SELECT 語句子句：GROUP BY 子句和 HAVING 子句。

一、數據分組

從 如何使用 SQL AVG、COUNT、MAX、MIN 和 SUM 彙總數據 中得知，使用 SQL 聚集函數可以彙總數據。這樣，我們就能夠對行進行計數，計算和與平均數，不檢索所有數據就獲得最大值和最小值。

目前為止的所有計算都是在表的所有數據或匹配特定的 WHERE 子句的數據上進行的。比如下麵的例子返回供應商 DLL01 提供的產品數目：

SELECT COUNT(*) AS num_prods
FROM Products
WHERE vend_id = 'DLL01';

輸出：

num_prods
-----------
4

如果要返回每個供應商提供的產品數目，該怎麼辦？或者返回只提供一項產品的供應商的產品，或者返回提供 10 個以上產品的供應商的產品，怎麼辦？

這就是分組大顯身手的時候了。使用分組可以將數據分為多個邏輯組，對每個組進行聚集計算。

二、創建分組

分組是使用 SELECT 語句的 GROUP BY 子句建立的。理解分組的最好辦法是看一個例子：

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;

輸出：

vend_id     num_prods
-------     ---------
BRS01       3
DLL01       4
FNG01       2

上面的 SELECT 語句指定了兩個列：vend_id 包含產品供應商的 ID，num_prods 為計算欄位（用 COUNT(*) 函數建立）。

GROUP BY 子句指示 DBMS 按 vend_id 排序並分組數據。這就會對每個 vend_id 而不是整個表計算 num_prods 一次。

從輸出中可以看到，供應商 BRS01 有 3 個產品，供應商 DLL01 有 4 個產品，而供應商 FNG01 有 2 個產品。

因為使用了 GROUP BY，就不必指定要計算和估值的每個組了。系統會自動完成。GROUP BY 子句指示 DBMS 分組數據，然後對每個組而不是整個結果集進行聚集。

在使用 GROUP BY 子句前，需要知道一些重要的規定。

  • GROUP BY 子句可以包含任意數目的列，因而可以對分組進行嵌套，更細緻地進行數據分組。
  • 如果在 GROUP BY 子句中嵌套了分組，數據將在最後指定的分組上進行彙總。換句話說，在建立分組時，指定的所有列都一起計算（所以不能從個別的列取回數據）。
  • GROUP BY 子句中列出的每一列都必須是檢索列或有效的表達式（但不能是聚集函數）。如果在 SELECT 中使用表達式，則必須在 GROUP BY 子句中指定相同的表達式。不能使用別名。
  • 大多數 SQL 實現不允許 GROUP BY 列帶有長度可變的數據類型（如文本或備註型欄位）。
  • 除聚集計算語句外，SELECT 語句中的每一列都必須在 GROUP BY 子句中給出。
  • 如果分組列中包含具有 NULL 值的行，則 NULL 將作為一個分組返回。如果列中有多行 NULL 值，它們將分為一組。
  • GROUP BY 子句必須出現在 WHERE 子句之後，ORDER BY 子句之前。

提示：ALL 子句

Microsoft SQL Server 等有些 SQL 實現在 GROUP BY 中支持可選的 ALL 子句。這個子句可用來返回所有分組，即使是沒有匹配行的分組也返回（在此情況下，聚集將返回 NULL）。

具體的 DBMS 是否支持 ALL，請參閱相應的文檔。

註意：通過相對位置指定列

有的 SQL 實現允許根據 SELECT 列表中的位置指定 GROUP BY 的列。例如，GROUP BY 2, 1 可表示按選擇的第二個列分組，然後再按第一個列分組。

雖然這種速記語法很方便，但並非所有 SQL 實現都支持，並且使用它容易在編輯 SQL 語句時出錯。

三、過濾分組

除了能用 GROUP BY 分組數據外，SQL 還允許過濾分組，規定包括哪些分組，排除哪些分組。例如，你可能想要列出至少有兩個訂單的所有顧客。為此，必須基於完整的分組而不是個別的行進行過濾。

我們已經看到了 WHERE 子句的作用（如何使用 SQL WHERE 過濾返回的數據 提及）。

但是，在這個例子中 WHERE 不能完成任務，因為 WHERE 過濾指定的是行而不是分組。事實上，WHERE 沒有分組的概念。

那麼，不使用 WHERE 使用什麼呢？SQL 為此提供了另一個子句，就是 HAVING 子句。HAVING 非常類似於 WHERE。

事實上，目前為止所學過的所有類型的 WHERE 子句都可以用 HAVING 來替代。唯一的差別是，WHERE 過濾行，而 HAVING 過濾分組。

提示：HAVING 支持所有 WHERE 操作符

在 如何使用 SQL WHERE 過濾返回的數據 和 如何使用 SQL AND、OR、IN 和 NOT 過濾返回的數據 中，我們學習了 WHERE 子句的條件（包括通配符條件和帶多個操作符的子句）。

學過的這些有關 WHERE 的所有技術和選項都適用於 HAVING。它們的句法是相同的，只是關鍵字有差別。

那麼，怎麼過濾分組呢？請看以下的例子：

SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

輸出：

cust_id        orders
----------     -----------
1000000001     2

這條 SELECT 語句的前三行類似於上面的語句。最後一行增加了 HAVING 子句，它過濾 COUNT(*) >= 2（兩個以上訂單）的那些分組。

可以看到，WHERE 子句在這裡不起作用，因為過濾是基於分組聚集值，而不是特定行的值。

說明：HAVING 和 WHERE 的差別

這裡有另一種理解方法，WHERE 在數據分組前進行過濾，HAVING 在數據分組後進行過濾。

這是一個重要的區別，WHERE 排除的行不包括在分組中。這可能會改變計算值，從而影響 HAVING 子句中基於這些值過濾掉的分組。

那麼，有沒有在一條語句中同時使用 WHERE 和 HAVING 子句的需要呢？

事實上，確實有。假如想進一步過濾上面的語句，使它返回過去 12 個月內具有兩個以上訂單的顧客。

為此，可增加一條 WHERE 子句，過濾出過去 12 個月內下過的訂單，然後再增加 HAVING 子句過濾出具有兩個以上訂單的分組。

為了更好地理解，來看下麵的例子，它列出具有兩個以上產品且其價格大於等於 4 的供應商：

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;

輸出：

vend_id     num_prods
-------     -----------
BRS01       3
FNG01       2

這條語句中，第一行是使用了聚集函數的基本 SELECT 語句，很像前面的例子。

WHERE 子句過濾所有 prod_price 至少為 4 的行，然後按 vend_id 分組數據，HAVING 子句過濾計數為 2 或 2 以上的分組。

如果沒有 WHERE 子句，就會多檢索出一行（供應商 DLL01，銷售 4 個產品，價格都在 4 以下）：

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id
HAVING COUNT(*) >= 2;

輸出：

vend_id     num_prods
-------     -----------
BRS01       3
DLL01       4
FNG01       2

說明：使用 HAVING 和 WHERE

HAVING 與 WHERE 非常類似，如果不指定 GROUP BY，則大多數 DBMS 會同等對待它們。

不過，你自己要能區分這一點。使用 HAVING 時應該結合 GROUP BY 子句，而 WHERE 子句用於標準的行級過濾。

四、分組和排序

GROUP BY 和 ORDER BY 經常完成相同的工作，但它們非常不同，理解這一點很重要。表 1 彙總了它們之間的差別。

表 1　ORDER BY 與 GROUP BY

ORDER BY GROUP BY
對產生的輸出排序 對行分組，但輸出可能不是分組的順序
任意列都可以使用（甚至非選擇的列也可以使用） 只可能使用選擇列或表達式列，而且必須使用每個選擇列表達式
不一定需要 如果與聚集函數一起使用列（或表達式），則必須使用

表 1 中列出的第一項差別極為重要。我們經常發現，用 GROUP BY 分組的數據確實是以分組順序輸出的。但並不總是這樣，這不是 SQL 規範所要求的。

此外，即使特定的 DBMS 總是按給出的 GROUP BY 子句排序數據，用戶也可能會要求以不同的順序排序。就因為你以某種方式分組數據（獲得特定的分組聚集值），並不表示你需要以相同的方式排序輸出。

應該提供明確的 ORDER BY 子句，即使其效果等同於 GROUP BY 子句。

提示：不要忘記 ORDER BY

一般在使用 GROUP BY 子句時，應該也給出 ORDER BY 子句。這是保證數據正確排序的唯一方法。千萬不要僅依賴 GROUP BY 排序數據。

為說明 GROUP BY 和 ORDER BY 的使用方法，來看一個例子。

下麵的 SELECT 語句類似於前面那些例子。它檢索包含三個或更多物品的訂單號和訂購物品的數目：

SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3;

輸出：

order_num     items
---------     -----
20006         3
20007         5
20008         5
20009         3

要按訂購物品的數目排序輸出，需要添加 ORDER BY 子句，如下所示：

SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;

輸出：

order_num     items
---------     -----
20006         3
20009         3
20007         5
20008         5

在這個例子中，使用 GROUP BY 子句按訂單號（order_num 列）分組數據，以便 COUNT(*) 函數能夠返回每個訂單中的物品數目。

HAVING 子句過濾數據，使得只返回包含三個或更多物品的訂單。最後，用 ORDER BY 子句排序輸出。

五、SELECT 子句順序

下麵回顧一下 SELECT 語句中子句的順序。表 2 以在 SELECT 語句中使用時必須遵循的次序，列出迄今為止所學過的子句。

表 2 SELECT 子句及其順序

子句 說明 是否必須使用
SELECT 要返回的列或表達式 是
FROM 從中檢索數據的表 僅在從表選擇數據時使用
WHERE 行級過濾 否
GROUP BY 分組說明 僅在按組計算聚集時使用
HAVING 組級過濾 否
ORDER BY 輸出排序順序 否

六、小結

本文介紹瞭如何使用 GROUP BY 子句對多組數據進行彙總計算，返回每個組的結果。

我們看到瞭如何使用 HAVING 子句過濾特定的組，還知道了 ORDER BY 和 GROUP BY 之間以及 WHERE 和 HAVING 之間的差異。

原文鏈接：https://www.developerastrid.com/sql/sql-grouping-data/

（完）


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

-Advertisement-
Play Games
更多相關文章
  • Macrium Reflect 取代ghost
    本文例子參考《STM32單片機開發實例——基於Proteus虛擬模擬與HAL/LL庫》 源代碼：https://github.com/LanLinnet/STM33F103R6 項目要求 實現通過串口助手發送單位元組數據，單片機收到數據後，交換高4位與低4位，將新的數據通過串口發回串口助手。例如，串口 ...
  • Docker基本命令
    前言： 這是《VMware 虛擬機圖文安裝和配置 Rocky Linux 8.5 教程》一文的姐妹篇教程，如果你需要閱讀它，請點擊這裡。 2020 年，CentOS 宣佈：計劃未來將重心從 CentOS Linux 轉移到 CentOS Stream。CentOS 8 的生命周期已於 2021 年 ...
  • 大數據Hadoop之——Azkaban API詳解
    一、Azkaban API概述 通常，企業里一般不用使用web UI去設置或者執行任務，只是單純的在頁面上查看任務或者排查問題，更多的是通過Azkaban API去提交執行任務計劃。Azkaban提供了一些常用的API操作，可以通過curl或其他HTTP請求客戶端訪問。但是API調用都需要首先進行適 ...
  • 楊玉基：知識圖譜在美團推薦場景中的應用
    導讀： 美團是一個生活服務領域的平臺，需要大量知識來理解用戶的搜索意圖，同時對於商家側我們也需要利用現有的知識對海量信息進行挖掘與提取，進而優化用戶體驗。今天分享的主題是知識圖譜在美團推薦場景中的應用。主要包括以下幾方面內容： 美團知識圖譜介紹 美團推薦場景介紹 美團推薦中的知識應用 總結與展望 - ...
  • 為什麼列式存儲會被廣泛用在 OLAP 中？
    大家好，我是大D。 不知是否有小伙伴們疑問，為什麼列式存儲會廣泛地應用在 OLAP 領域，和行式存儲相比，它的優勢在哪裡？今天我們一起來對比下這兩種存儲方式的差別。 其實，列式存儲並不是一項新技術，最早可以追溯到 1983 年的論文 Cantor。然而，受限於早期的硬體條件和應用場景，傳統的事務型數 ...
  • 一文帶你讀懂 Hbase 的架構組成
    hi，大家好，我是大D。今天咱們繼續深挖一下 HBase 的架構組成。 Hbase 作為 NoSQL 資料庫的代表，屬於三駕馬車之一 BigTable 的對應實現，HBase 的出現很好地彌補了大數據快速查詢能力的空缺。在前面咱們也有介紹過 HBase 的數據模型，感興趣的小伙伴可以翻看下。談談你對 ...
  • 什麼是 SQL 子查詢，如何使用 SQL 子查詢
    本文介紹什麼是 SQL 子查詢，如何使用它們。子查詢常用於 WHERE 子句的 IN 操作符中，以及用來填充計算列。 一、子查詢 SELECT 語句是 SQL 的查詢。我們迄今為止所看到的所有 SELECT 語句都是簡單查詢，即從單個資料庫表中檢索數據的單條語句。 查詢（query） 任何 SQL ...
  • 不可不知的 MySQL 升級利器及 5.7 升級到 8.0 的註意事項
    資料庫升級，是一項讓人喜憂參半的工程。喜的是，通過升級，可以享受新版本帶來的新特性及性能提升。憂的是，新版本可能與老的版本不相容，不相容主要體現在以下三方面： 語法不相容。 語義不相容。同一個SQL，在新老版本執行結果不一致。 新版本的查詢性能更差。 所以，在對線上資料庫進行升級之前，一般都會在測試 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類