快捷導(dǎo)航

mysql的group by函數(shù)使用方法

更新時間：2025年08月18日 10:07:28 作者：detayun

MySQL中的GROUP BY子句用于將查詢結(jié)果按一個或多個列進行分組,通常與聚合函數(shù),實現(xiàn)數(shù)據(jù)的分組統(tǒng)計,本文給大家介紹mysql的group by函數(shù)使用方法,感興趣的朋友跟隨小編一起看看吧

1. 基礎(chǔ)語法

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1
ORDER BY column1;

2. 核心功能

(1) 單列分組

統(tǒng)計每個部門的員工數(shù)量：

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

(2) 多列分組

統(tǒng)計每個部門+職位的平均工資：

SELECT department, job_title, AVG(salary) AS avg_salary
FROM employees
GROUP BY department, job_title;

(3) 結(jié)合聚合函數(shù)

COUNT()：計數(shù)
SUM()：求和
AVG()：平均值
MAX()/MIN()：極值
GROUP_CONCAT()：合并分組內(nèi)的字符串

3. 過濾分組：HAVING

WHERE過濾行，HAVING過濾分組：

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 5000;  -- 篩選平均工資>5000的部門

4. 常見錯誤

(1) SELECT非聚合列未分組

? 錯誤寫法：

SELECT department, employee_name, COUNT(*)
FROM employees
GROUP BY department;

? 正確寫法：

SELECT department, GROUP_CONCAT(employee_name), COUNT(*)
FROM employees
GROUP BY department;

(2) HAVING與WHERE混淆

WHERE在分組前過濾（如過濾原始數(shù)據(jù)）
HAVING在分組后過濾（如過濾統(tǒng)計結(jié)果）

5. 高級用法

(1) WITH ROLLUP（生成小計/總計）

SELECT department, SUM(salary)
FROM employees
GROUP BY department WITH ROLLUP;

結(jié)果會多出一行NULL，表示所有部門的總計。

(2) 分組后排序

SELECT department, COUNT(*)
FROM employees
GROUP BY department
ORDER BY COUNT(*) DESC;  -- 按員工數(shù)量降序排列

6. 注意事項

MySQL 5.7+默認啟用ONLY_FULL_GROUP_BY模式，要求SELECT中的非聚合列必須出現(xiàn)在GROUP BY中。

可通過ANY_VALUE()函數(shù)繞過嚴格模式：

SELECT ANY_VALUE(employee_name), department, COUNT(*)
FROM employees
GROUP BY department;

示例場景

統(tǒng)計2023年每月銷售額及訂單數(shù)：

SELECT 
    MONTH(order_date) AS month,
    SUM(amount) AS total_sales,
    COUNT(order_id) AS order_count
FROM orders
WHERE YEAR(order_date) = 2023
GROUP BY MONTH(order_date)
ORDER BY month;

通過靈活組合GROUP BY和聚合函數(shù)，可以高效實現(xiàn)復(fù)雜的數(shù)據(jù)統(tǒng)計分析需求。

到此這篇關(guān)于mysql的group by函數(shù)怎么使用的文章就介紹到這了,更多相關(guān)mysql group by函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片