SQL 基礎知識- 聚合和排序

序

　　這是《SQL 基礎知識梳理（二） - 查詢基礎》的下篇。

目錄

• 對錶進行聚合查詢
• 對錶進行分組
• 為聚合結果指定條件
• 對查詢結果進行排序

一、對錶進行聚合查詢

　　1.聚合函數

　　　　（1）5 個常用函數：

　　　　　　①COUNT：計算表中的記錄（行）數。

　　　　　　②SUM：計算表中數值列的數據合計值。

　　　　　　③AVG：計算表中數值列的數據平均值。

　　　　　　④MAX：求出表中任意列中數據的最大值。

　　　　　　⑤MIN：求出表中任意列中數據的最小值。

　　　　（2）聚合：將多行彙總成一行。

圖1-1 Shohin 表

　　2.計算表中數據的行數　

<code>--示例
SELECT COUNT(*)　　-- *：參數，這裡代表全部列
FROM dbo.Shohin;/<code>

　　3.計算 NULL 以外數據的行數

　　將 COUNT(*) 的參數改成指定對象的列，就可以得到該列的非 NULL 行數。

<code>SELECT COUNT(shiire_tanka)
FROM dbo.Shohin;/<code>

只計算非 NULL 的行

　　【備註】除了 COUNT 函數，其它函數不能將星號作為參數。

　　【備註】COUNT 函數的結果根據參數的不同而不同。COUNT(*) 會得到包含 NULL 的數據行數，而 COUNT() 會得到 NULL 之外的數據行數。

　　4.計算合計值

<code>SELECT SUM(hanbai_tanka) AS sum_hanbai_tanka,    --總和
    AVG(hanbai_tanka) AS avg_hanbai_tanka,        --平均
    MAX(hanbai_tanka) AS max_hanbai_tanka,        --最大
    MIN(hanbai_tanka) AS min_hanbai_tanka        --最小
FROM dbo.Shohin;/<code>

　　【備註】所有的聚合函數，如果以列名為參數，會無視 NULL 值所在的行。

<code>SELECT MAX(torokubi),　　--torokubi 為日期
    MIN(torokubi)
FROM dbo.Shohin/<code>

　　【備註】MAX/MIN 函數幾乎適用於所有數據類型的列。SUM/AVG 函數只適用於數值類型的列。

　　5.使用聚合函數刪除重複值（關鍵字 DISTINCT）

<code>--示例1：計算去除重複數據後的數據行數
SELECT COUNT(DISTINCT shohin_bunrui)
FROM dbo.Shohin;

--示例2：先計算數據行數再刪除重複數據的結果 

SELECT DISTINCT COUNT(shohin_bunrui)
FROM dbo.Shohin;/<code>

　　【備註】在聚合函數的參數中使用 DISTINCT（示例1），可以刪除重複數據。DISTINCT 不僅限於 COUNT 函數，所有的聚合函數都可以使用。

二、對錶進行分組

　　1.GROUP BY 子句

<code>--語法：
--SELECT , , ...
--FROM 
--GROUP BY , , ...;/<code>

<code>--示例
SELECT shohin_bunrui AS '商品種類',
    COUNT(*) AS '數量'
FROM dbo.Shohin
GROUP BY shohin_bunrui;/<code>

　　【備註】GROUP BY 子句中指定的列稱為“聚合鍵”或“分組列”。

　　【子句的書寫順序（暫定）】SELECT --> FROM --> WHERE --> GROUP BY

　　2.聚合鍵中包含 NULL 的情況

<code>SELECT shiire_tanka, COUNT(*)
FROM dbo.Shohin
GROUP BY shiire_tanka;/<code>

　　【備註】聚合鍵中包含 NULL 時，在結果中也會以 NULL 行的形式表現出來。

　　3.WHERE 對 GROUP BY 執行結果的影響

<code>--語法
--SELECT , , ...
--FROM 
--WHERE 
--GROUP BY , , .../<code>

<code>SELECT shiire_tanka, COUNT(*)
FROM dbo.Shohin
WHERE shohin_bunrui = '衣服'
GROUP BY shiire_tanka/<code>

　　這裡是先根據 WHERE 子句指定的條件進行過濾，然後再進行聚合處理。

　　【執行順序】FROM --> WHERE --> GROUP BY --> SELECT。這裡是執行順序，跟之前的書寫順序是不一樣的。

　　4.與聚合函數和 GROUP BY 子句有關的常見錯誤

　　（1）易錯：在 SELECT 子句中書寫了多餘的列

　　　　SELECT 子句只能存在以下三種元素：

　　　　　　①常數

　　　　　　②聚合函數

　　　　　　③GROUP BY 子句中指定的列名（即聚合鍵）

易錯點1

　　【總結】使用 GROUP BY 子句時，SELECT 子句不能出現聚合鍵之外的列名。

　　（2）易錯：在 GROUP BY 子句中寫了列的別名　　　

易錯點2

　　回顧之前說的執行順序，SELECT 子句是在 GROUP BY 子句之後執行。所以執行到 GROUP BY 子句時無法識別別名。

　　【總結】GROUP BY 子句不能使用 SELECT 子句中定義的別名。

　　（3）易錯：GROUP BY 子句的結果能排序嗎？

　　【解答】它是隨機的。如果想排序，請使用 ORDER BY 子句。

　　【總結】GROUP BY 子句結果的顯示是無序的。

　　（4）易錯：在 WHERE 子句中使用聚合函數

易錯點3

　　【總結】只有 SELECT 子句和 HAVING 子句（以及 ORDER BY 子句）中能夠使用聚合函數。

三、為聚合結果指定條件

　　1.HAVING 子句

　　WHERE 子句智能指定記錄（行）的條件，而不能用來指定組的條件。

　　【備註】HAVING 是 HAVE（擁有）的現在分詞。

<code>--語法：
--SELECT , , ...
--FROM 
--GROUP BY , , ...
--HAVING /<code>

　　【書寫順序】SELECT --> FROM --> WHERE --> GROUP BY --> HAVING

<code>SELECT shohin_bunrui, COUNT(*)
FROM dbo.Shohin
GROUP BY shohin_bunrui
HAVING COUNT(*) = 2/<code>

從通過商品種類進行聚合分組後的結果中，取出“包含數據的行數為 2 行”的數據

　　2.HAVING 子句的構成要素

　　　　（1）3 要素：

　　　　　　①常數

　　　　　　②聚合函數

　　　　　　③GROUP BY 子句中指定的列名（即聚合鍵）

易錯用法

　　3.HAVING 與 WHERE

　　有些條件可以寫在 HAVING 子句中，又可以寫在 WHERE 子句中。這些條件就是聚合鍵所對應的條件。

結果一樣

　　【建議】雖然結果一樣，聚合鍵對應的條件應該寫在 WHERE 子句中，不是 HAVING 子句中。

　　【理由】①WHERE 子句的執行速度比 HAVING 快。

　　　　　　②意義：WHERE 子句 = 指定行所對應的條件，HAVING 子句 = 指定組所對應的條件。

四、對查詢結果進行排序

　　1.ORDER BY 子句

<code>--語法：
--SELECT , , ...
--FROM 
--ORDER BY , , .../<code>

<code>SELECT shohin_id, hanbai_tanka
FROM dbo.Shohin
ORDER BY hanbai_tanka;    --升序排列/<code>

銷售單價由低到高（升序）

　　排序鍵：ORDER BY 子句中書寫的列名。

　　【書寫順序】SELECT --> FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY

　　2.升序（ASC）和降序（DESC）：

<code>SELECT shohin_id, hanbai_tanka
FROM dbo.Shohin
ORDER BY hanbai_tanka DESC;    --降序排列/<code>

　　【備註】ORDER BY 子句中排列順序時會默認使用升序（ASC）進行排列。

　　3.指定多個排序鍵

<code>SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka
FROM dbo.Shohin
ORDER BY hanbai_tanka, shohin_id;/<code>

　　4.NULL 值的順序：排序鍵中包含 NULL 時，會在開頭或末尾進行彙總。

　　5.在排序鍵中使用 SELECT 子句中的別名

<code>SELECT shohin_id AS id, shohin_mei, hanbai_tanka AS ht
FROM dbo.Shohin
ORDER BY ht, id;/<code>

　　【執行順序】FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY

　　【備註】ORDER BY 子句可以使用 SELECT 子句中定義的別名，GROUP BY 子句不能使用別名。

　　6.ORDER BY 子句中使用聚合函數

<code>SELECT shohin_bunrui, COUNT(*)
FROM dbo.Shohin 

GROUP BY shohin_bunrui
ORDER BY COUNT(*);/<code>

　　7.不建議使用列的編號進行排序，雖然可以

<code>SELECT shohin_id ,
       shohin_mei ,
       shohin_bunrui ,
       hanbai_tanka ,
       shiire_tanka ,
       torokubi
FROM dbo.Shohin
ORDER BY hanbai_tanka DESC, shohin_id;

SELECT shohin_id ,
       shohin_mei ,
       shohin_bunrui ,
       hanbai_tanka ,
       shiire_tanka ,
       torokubi
FROM dbo.Shohin
ORDER BY 4 DESC, 1;　　--這裡使用列的編號，由於閱讀不便，不推薦使用/<code>

結果是一樣的

　　【備註】在 ORDER BY 子句中不要使用列的編號。

傳送門

　　《SQL 基礎知識梳理（一） - 數據庫與 SQL》

　　《SQL 基礎知識梳理（二） - 查詢基礎》

備註

　　這裡採用 MS SQL Server 進行驗證，不保證所有的 DBMS 執行結果正確。

閱讀更多 帥氣十足的陳杰 的文章

關鍵字: 聚合 SQL 排序