如何用SQL分组汇总数据

在 SQL 中，分组汇总数据通常使用 GROUP BY 语句配合聚合函数（如 COUNT、SUM、AVG、MAX、MIN）来实现。

下面从基础到进阶，系统讲解用法。

一、基本语法

SELECT 分组列, 聚合函数(列)
FROM 表名
WHERE 条件
GROUP BY 分组列
ORDER BY 排序列;

执行顺序：

1. FROM
2. WHERE
3. GROUP BY
4. SELECT
5. ORDER BY

二、常见聚合函数

三、基础示例

1️⃣ 按部门统计人数

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

2️⃣ 按部门统计工资总和

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

3️⃣ 多列分组

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

四、分组后筛选（HAVING）

⚠️ WHERE 不能用于聚合结果，要用 HAVING

统计人数大于 10 的部门

SELECT department, COUNT(*) AS cnt
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

平均工资大于 8000 的部门

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 8000;

五、分组 + 排序

SELECT department, SUM(salary) AS total
FROM employees
GROUP BY department
ORDER BY total DESC;

六、常见实战场景

✅ 每日订单数

SELECT order_date, COUNT(*) AS order_cnt
FROM orders
GROUP BY order_date;

✅ 每类产品销售总额

SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

✅ 每个用户最近一笔订单金额

SELECT user_id, MAX(order_amount) AS max_amount
FROM orders
GROUP BY user_id;

七、易错点提醒 ⚠️

1. SELECT 中的非聚合列必须出现在 GROUP BY 中

-- 错误
SELECT department, name FROM employees GROUP BY department;

-- 正确
SELECT department, COUNT(*) FROM employees GROUP BY department;

2. WHERE 在分组前过滤，HAVING 在分组后过滤

八、进阶（可选）

• GROUP BY ROLLUP / CUBE（小计、总计）
• GROUPING SETS
• 窗口函数（SUM() OVER (PARTITION BY ...)）

如果你有具体表结构或业务场景，我可以直接帮你写 SQL。