MySQL學習之分組查詢的用法詳解

2022-07-28 14:03:49

該章節來開始學習分組查詢，上一章節我們學習了聚合函數，預設統計的是全表範圍內的資料，配合上 WHERE 就能夠縮小統計的範圍了。但是這並不能滿足我們的要求，比如說我們按照之前的資料表查詢每個部門的平均底薪是多少？這樣的記錄就需要針對部門編號進行分組了。根據分組的情況統計分組內的最大值、最小值、平均值等等。如此就能夠滿足剛剛提到的 “查詢每個部門的平均底薪” 這樣的需求了，另外，“分組查詢” 是 SQL 中很重要的一個語法，大家一定要好好掌握它。

為什麼要分組

上面也提到，聚合函數預設是對全表範圍內的資料做統計，在一些特定的場景下不太適用，就比如對資料分別進行統計的場景。

由於聚合函數的這樣的侷限性，也就產生了分組的概念，於是就有了分組的語法。

分組的語法是通過 “GROUP BY” 來實現的。

"GROUP BY" 子句的作用是通過一定的規則將一個資料集劃分成若干個小的區域，然後再針對每個小區域分別進行資料彙總處理

分組語句演示案例：（計算每一個部門的平均底薪）

SELECT deptno, AVG(sal) 
FROM t_emp
GROUP BY deptno;

-- 利用 GROUP BY 子句將 deptno 進行分組，在利用 AVG() 聚合函數計算各個 deptno(部門) 的平均月薪

這裡的小數，可能看著不太舒服，我們可以使用 ROUND() 函數將平均工資四捨五入變成整數。

SELECT deptno, ROUND(AVG(sal))
FROM t_emp
GROUP BY deptno;

逐級分組

有的時候僅有大的分組還不夠，還需要在大的分組裡面劃分出曉得分組，然後再執行統計計算，於是就有了逐級分組。

什麼是逐級分組？ MySQL 資料庫支援多列分組條件，執行的時候按照多列去依次執行，這就是逐級分組。

範例如下：（查詢每個部門裡，每種職位的人員數量和平均工資。）

SELECT deptno, job, COUNT(*), AVG(sal)
FROM t_emp
GROUP BY deptno, job
ORDER BY deptno;

-- 首先要按照部門對員工進行分組,在部門裡，還要按照職務去分組; 就是 "GROUP BY deptno, job"
-- 然後再用 聚合函數的 AVG 計算平均的月薪; 就是 "SELECT deptno, job, COUNT(*), AVG(sal)"
-- 最後按照 deptno(部門編號) 排序，使用 ORDER BY 進行升序排序。

逐級分組對 SELECT 子句的要求

查詢語句中如果包含有 “GROUP BY” 子句，那麼 “SELECT” 子句中的內容就必須要遵守以下規定
"SELECT" 子句中可以包含聚合函數，或者 "GROUP BY" 子句的分組列，其餘內容均不可以出現在 "SELECT" 子句中
SQL 範例如下：（遵守規定範例）

SELECT deptno, COUNT(*), AVG(sal)
FROM t_emp
GROUP BY deptno;

SQL 範例如下：（不遵守規定範例）

SELECT deptno, COUNT(*), AVG(sal), sal
FROM t_emp
GROUP BY deptno;

-- 這條語句是無法執行成功的，因為在 "SELECT" 子句中，有一個 "sal" 的欄位
-- 這個 "sal" 欄位 沒有在 GROUP BY 中去分組，本身也沒有聚合函數，就是一個普通的欄位

-- 造成無法執行、報錯的原因是因為，"SELECT deptno, COUNT(*), AVG(sal), sal" 
-- 中的 "deptno、COUNT(*)、AVG(sal)" 返回的是 "GROUP BY" 一個結果集分組的同級資訊；
-- 而 "sal" 欄位又是多條匹配記錄，前後肯定是匹配不上的，所以這個 SQL 語句是無法執行成功的。

-- 同時因為標準的 SQL 規定，對錶進行聚合查詢的時候，只能在 SELECT 子句中寫下面 3 種內容：
-- 通過 GROUP BY 子句指定的聚合鍵、聚合函數(SUM 、AVG 等)、常數。
-- 所以在使用 SQL 語句記性資料表查詢時，一定要嚴格遵守 SQL 的語法規定。

對分組結果集再次做彙總計算

來看一個範例：（查詢員工表中各個部門的人數，各個部門的平均月薪、最大月薪、最小月薪、按照員工號進行排序，並針對各個部門再次做一個彙總統計。）

SELECT deptno, COUNT(*), AVG(sal), MAX(sal), MIN(sal)
FROM t_emp
GROUP BY deptno
WITH ROLLUP;

-- 這裡的 "WITH ROLLUP" 子句就是針對 "deptno"分組的結果集，再一次的進行彙總計算

PS：該 SQL 語句主要是為了體現 "WITH ROLLUP" 關鍵字的效果，是對聚合函數的再次執行彙總計算。

GROUP_CONCAT 函數

上文中的 "逐級分組對 SELECT 子句的要求" 部分的時候解釋了為什麼會有這樣的要求，就是聚合函數返回一條記錄的結果與非分組欄位的多條記錄的結果無法匹配。

如果想要想要匹配，那就要把非分組的欄位的多條記錄轉換成一條記錄，MySQL 提供的 GROUP_CONCAT 函數就可以將分組查詢中的非分組欄位中的多條記錄合併成一條記錄。

SQL 語句 "GROUP_CONCAT" 範例如下：（查詢每個部門內底薪超過 2000元的人數和員工姓名，這裡的員工姓名就是非分組的欄位）

SELECT deptno, GROUP_CONCAT(ename), AVG(sal),COUNT(*)
FROM t_emp
WHERE sal >= 2000
GROUP BY deptno;

-- 查詢員工表，篩選條件為月薪大於等於 2000 ，以 "deptno" 為分組
-- "ename" 欄位沒有分組，但是我們使用 "GROUP_CONCAT" 函數將 "ename" 的多條返回記錄轉換為一條記錄

GROUP BY 子句的執行順序

截止到目前為止，我們所學習的所有子句，執行順序如下：

FROM ---> WHERE ---> GROUP BY ---> SELECT ---> ORDER BY ---> LIMIT

到此這篇關於MySQL學習之分組查詢的用法詳解的文章就介紹到這了,更多相關MySQL分組查詢內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！