GROUP BY簡介

GROUP BY 子句是 在MySQL 中用于將查詢結(jié)果按照指定的列或表達(dá)式進(jìn)行分組的關(guān)鍵字。它通常與聚合函數(shù)一起使用，能夠?qū)γ總€(gè)分組進(jìn)行統(tǒng)計(jì)或計(jì)算，并在需要時(shí)進(jìn)行篩選，是處理數(shù)據(jù)庫中大量數(shù)據(jù)并生成匯總報(bào)表的重要工具。

常用的聚合函數(shù)有：count() 計(jì)數(shù), sum() 求和 , avg() 求平均值, max() 求最大值, min()求最小值。

基本用法

我們拿一張學(xué)生表舉例

創(chuàng)建表：

CREATE TABLE students (
        student_id INT AUTO_INCREMENT PRIMARY KEY, -- 學(xué)生ID，自增主鍵
        name VARCHAR(50), -- 學(xué)生姓名
        major VARCHAR(50), -- 專業(yè)
        grade VARCHAR(10), -- 年級(jí)
        age INT, -- 年齡
        admission_year datetime -- 入學(xué)日期
)

插入數(shù)據(jù)：

INSERT INTO students (name, major, grade, age, admission_year) VALUES
('張三', '計(jì)算機(jī)科學(xué)', '大一', 18, '2024-01-01 00:00:00'),
('李四', '數(shù)學(xué)', '大一', 19, '2024-01-01 00:00:00'),
('王五', '物理', '大二', 20, '2023-01-01 00:00:00'),
('趙六', '化學(xué)', '大二', 19, '2023-01-01 00:00:00'),
('小明', '生物', '大三', 21, '2022-01-01 00:00:00'),
('小紅', '歷史', '大三', 22, '2022-01-01 00:00:00'),
('小李', '地理', '大四', 23, '2021-01-01 00:00:00'),
('小張', '經(jīng)濟(jì)學(xué)', '大四', 22, '2021-01-01 00:00:00'),
('小王', '文學(xué)', '大一', 18, '2024-01-01 00:00:00'),
('小劉', '計(jì)算機(jī)科學(xué)', '大一', 19, '2024-01-01 00:00:00'),
('小楊', '數(shù)學(xué)', '大二', 20, '2023-01-01 00:00:00'),
('小白', '物理', '大二', 19, '2023-01-01 00:00:00'),
('小黑', '化學(xué)', '大三', 21, '2022-01-01 00:00:00'),
('小貓', '生物', '大三', 22, '2022-01-01 00:00:00'),
('小狗', '歷史', '大四', 23, '2021-01-01 00:00:00'),
('小鳥', '地理', '大四', 22, '2021-01-01 00:00:00'),
('小魚', '經(jīng)濟(jì)學(xué)', '大一', 18, '2024-01-01 00:00:00'),
('小蟲', '文學(xué)', '大一', 19, '2024-01-01 00:00:00'),
('小兔', '計(jì)算機(jī)科學(xué)', '大二', 20, '2023-01-01 00:00:00'),
('小雞', '數(shù)學(xué)', '大二', 19, '2023-01-01 00:00:00'),
('小鴨', '物理', '大三', 21, '2022-01-01 00:00:00'),
('小狐', '化學(xué)', '大三', 22, '2022-01-01 00:00:00'),
('小牛', '生物', '大四', 23, '2021-01-01 00:00:00'),
('小馬', '歷史', '大四', 22, '2021-01-01 00:00:00'),
('小羊', '地理', '大一', 18, '2024-01-01 00:00:00'),
('小豬', '經(jīng)濟(jì)學(xué)', '大一', 19, '2024-01-01 00:00:00'),
('小狗', '文學(xué)', '大二', 20, '2023-01-01 00:00:00'),
('小雞', '計(jì)算機(jī)科學(xué)', '大二', 19, '2023-01-01 00:00:00'),
('小鴨', '數(shù)學(xué)', '大三', 21, '2022-01-01 00:00:00'),
('小貓', '物理', '大三', 22, '2022-01-01 00:00:00'),
('小猴', '化學(xué)', '大四', 23, '2021-01-01 00:00:00'),
('小狗', '生物', '大四', 22, '2021-01-01 00:00:00'),
('小鳥', '歷史', '大一', 18, '2024-01-01 00:00:00'),
('小貓', '地理', '大一', 19, '2024-01-01 00:00:00'),
('小魚', '經(jīng)濟(jì)學(xué)', '大二', 20, '2023-01-01 00:00:00'),
('小蟲', '文學(xué)', '大二', 19, '2023-01-01 00:00:00');

單列分組

例如：按照年級(jí)對(duì)學(xué)生進(jìn)行分組，并計(jì)算每個(gè)年級(jí)的學(xué)生數(shù)量。

SELECT grade, COUNT(*)
FROM students
GROUP BY grade;
 

多列分組

例如：按照年級(jí)和年齡對(duì)學(xué)生進(jìn)行分組，并計(jì)算每個(gè)年級(jí)、年齡組合的學(xué)生數(shù)量。

SELECT grade, age, COUNT(*)
FROM students
GROUP BY grade, age;

使用聚合函數(shù) 

例如：計(jì)算每個(gè)年級(jí)的學(xué)生平均年齡

SELECT grade, AVG(age)
FROM students
GROUP BY grade;

過濾分組結(jié)果 

HAVING 子句在 GROUP BY 之后對(duì)分組進(jìn)行過濾。它允許篩選哪些組將包含在結(jié)果中，類似于 WHERE 子句對(duì)行進(jìn)行過濾。通常，HAVING 子句用于過濾聚合后的結(jié)果，根據(jù)某些條件選擇性地包括或排除分組。

例如：篩選出平均年齡超過 20 歲的年級(jí)

SELECT grade, AVG(age)
FROM students
GROUP BY grade
HAVING AVG(age) > 20;

按表達(dá)式分組

例如：按照入學(xué)年份（在 "admission_year" 列中）對(duì)學(xué)生進(jìn)行分組，并計(jì)算每個(gè)入學(xué)年份的學(xué)生數(shù)量。

SELECT YEAR(admission_year), COUNT(*)
FROM students
GROUP BY YEAR(admission_year);

使用 GROUP BY 的排序 

例如：按照年級(jí)對(duì)學(xué)生進(jìn)行分組，并按照每個(gè)年級(jí)的學(xué)生數(shù)量從高到低排序。

SELECT grade, COUNT(*)
FROM students
GROUP BY grade
ORDER BY COUNT(*) DESC;

注意事項(xiàng)

遵循原則

確保在SELECT子句中使用的列都包含在GROUP BY子句中，或者是聚合函數(shù)的參數(shù)。否則，查詢可能會(huì)產(chǎn)生錯(cuò)誤的結(jié)果或語法錯(cuò)誤。

換句話說group by 有一個(gè)原則,就是 select 后面的所有列中,沒有使用聚合函數(shù)的列,必須出現(xiàn)在 group by 后面

正例：

一共select了grade ,age ,student_id三列，只有student_id列使用了count聚合函數(shù)，grade ,age列沒用聚合函數(shù)就必須跟在group by 后面

SELECT grade, age, COUNT(student_id) as 學(xué)生數(shù)量
FROM students
GROUP BY grade, age;

查詢結(jié)果返回了年紀(jì)跟年齡的所有組合下的學(xué)生數(shù)量。

反例：

在 SELECT 子句中，除了 COUNT(student_id) 使用了聚合函數(shù)外，其余的兩列 grade 和 age 都沒有使用聚合函數(shù)。但是在 GROUP BY 子句中，只列出了 grade 列，而沒有包括 age 列。因此，這個(gè)查詢違反了該原則。

SELECT grade, age, COUNT(student_id) as 學(xué)生數(shù)量
FROM students
GROUP BY grade;

由于違背了group by的原則，age列沒有跟在group by后面導(dǎo)致只查詢了不同年級(jí)的學(xué)生數(shù)量統(tǒng)計(jì)，然而結(jié)果出現(xiàn)的age列僅僅是對(duì)應(yīng)年級(jí)下第一個(gè)學(xué)生的年齡，這樣是沒有意義的，這樣的結(jié)果是混亂的。

使用能夠唯一標(biāo)識(shí)每個(gè)分組的字段或字段組合

正例：

比如專業(yè)，年級(jí)。

反例：

唯一標(biāo)識(shí)符字段：如果字段中的值對(duì)每個(gè)數(shù)據(jù)行都是唯一的，那么使用這樣的字段進(jìn)行 GROUP BY 將會(huì)使每個(gè)分組中只有一行數(shù)據(jù)，且分組數(shù)量大。

包含大量不同值的字段：如果某個(gè)字段的取值范圍非常廣泛，例如一個(gè)具有高基數(shù)（cardinality）的字段，使用它進(jìn)行 GROUP BY 可能會(huì)導(dǎo)致大量的小分組，從而使結(jié)果變得難以理解或者過于細(xì)粒度化。

文本字段：雖然您可以使用文本字段進(jìn)行 GROUP BY，但是它可能會(huì)導(dǎo)致分組的數(shù)量龐大，并且對(duì)結(jié)果的解釋會(huì)變得更加困難。在這種情況下，最好先對(duì)文本字段進(jìn)行分析或預(yù)處理，以便將其轉(zhuǎn)換為更具可分組性的特征。

包含 NULL 值的字段：如果一個(gè)字段大部分值都是 NULL，那么使用它進(jìn)行 GROUP BY 可能會(huì)使得 NULL 值形成一個(gè)單獨(dú)的分組，而其他分組則非常少。

性能

GROUP BY操作可能會(huì)導(dǎo)致查詢的性能下降，特別是在處理大量數(shù)據(jù)時(shí)。確保索引和適當(dāng)?shù)膬?yōu)化策略可以幫助提高查詢性能。

創(chuàng)建索引：為 GROUP BY 子句中的字段創(chuàng)建索引，這樣數(shù)據(jù)庫可以更快地定位并處理數(shù)據(jù)。如果您經(jīng)常使用某個(gè)字段進(jìn)行 GROUP BY，考慮為該字段創(chuàng)建索引以加快查詢速度。

使用覆蓋索引：創(chuàng)建覆蓋索引以覆蓋 GROUP BY 查詢中涉及的所有字段。這樣可以避免數(shù)據(jù)庫執(zhí)行額外的查找操作，從而提高性能。

限制結(jié)果集：在 GROUP BY 子句之前使用 WHERE 子句過濾數(shù)據(jù)，以減少處理的數(shù)據(jù)量。只選擇必要的數(shù)據(jù)行可以顯著提高查詢性能。

使用聚合函數(shù)：考慮使用聚合函數(shù)（如SUM、COUNT、AVG等）來減少數(shù)據(jù)量。盡量在 GROUP BY 之前使用聚合函數(shù)，以便減少處理的數(shù)據(jù)量。

避免使用復(fù)雜表達(dá)式：在 GROUP BY 子句中盡量避免使用復(fù)雜的表達(dá)式或函數(shù)。這些表達(dá)式可能會(huì)增加處理時(shí)間，并使索引失效。

注意數(shù)據(jù)類型

在MySQL中，雖然可以在幾乎任何數(shù)據(jù)類型的列上使用GROUP BY子句，但某些數(shù)據(jù)類型可能在實(shí)際應(yīng)用中帶來挑戰(zhàn)或性能問題。

適合分組的數(shù)據(jù)類型

• INT、BIGINT等整數(shù)類型：這些類型在進(jìn)行分組和比較時(shí)比較可靠。
• VARCHAR、CHAR等字符類型：字符類型在比較和分組時(shí)更具可預(yù)測性。

不適合分組的數(shù)據(jù)類型

BLOB和TEXT：用于存儲(chǔ)大型文本或二進(jìn)制數(shù)據(jù)，比較和分組時(shí)性能較差。

JSON：嵌套結(jié)構(gòu)復(fù)雜，直接比較不可靠，導(dǎo)致分組性能問題。

GEOMETRY：用于存儲(chǔ)空間數(shù)據(jù)，比較復(fù)雜且計(jì)算量大，難以進(jìn)行分組。

VARBINARY/BINARY：存儲(chǔ)二進(jìn)制數(shù)據(jù)，可能導(dǎo)致非字符內(nèi)容的比較問題。

FLOAT和DOUBLE：由于浮點(diǎn)數(shù)精度問題，分組結(jié)果可能不穩(wěn)定。

總的來說，適合在 GROUP BY 中使用的數(shù)據(jù)類型通常是具有明確順序或可數(shù)性質(zhì)的數(shù)據(jù)類型，而不是基于文本或二進(jìn)制的數(shù)據(jù)類型。

寫在最后

以上就是MySQL使用GROUP BY使用技巧和注意事項(xiàng)總結(jié)的詳細(xì)內(nèi)容，更多關(guān)于MySQL GROUP BY使用的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！