核心关键字：GROUP BY 分组、聚合函数（COUNT/SUM/AVG/MAX/MIN）、ORDER BY 排序
规则：
SELECT后面非聚合字段，必须出现在 GROUP BY 里
WHERE在分组前过滤；HAVING在分组后过滤（过滤聚合结果）
ORDER BY写在最后，ASC升序（默认）、DESC降序
示例表（order 订单表）
CREATE TABLE `t_order` (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT COMMENT '用户ID',
category VARCHAR(20) COMMENT '商品分类',
amount DECIMAL(10,2) COMMENT '金额',
create_time DATETIME
);
1. 基础：分组统计，按统计结果排序
需求：统计每个分类的订单数量，按数量从大到小排序
SELECT
category,
COUNT(*) AS order_count -- 订单数
FROM t_order
GROUP BY category
ORDER BY order_count DESC;
2. 求和统计 + 排序
需求：统计每个分类总销售额，销售额降序
SELECT
category,
SUM(amount) AS total_amount
FROM t_order
GROUP BY category
ORDER BY total_amount DESC;
3. 先 WHERE 过滤，再分组统计，再排序
只统计 2026 年的订单，按总金额排序
SELECT
category,
SUM(amount) AS total_amount
FROM t_order
WHERE create_time >= '2026-01-01'
GROUP BY category
ORDER BY total_amount DESC;
4. 分组后 HAVING 过滤 + 排序
只保留总金额大于 1000 的分类，再排序
❌ 不能用
WHERE total_amount>1000，聚合结果要用 HAVING
SELECT
category,
SUM(amount) AS total_amount
FROM t_order
GROUP BY category
HAVING total_amount > 1000
ORDER BY total_amount DESC;
5. 多字段分组（多个维度）
按【用户 + 分类】分组，统计下单次数，次数降序
SELECT
user_id,
category,
COUNT(*) AS cnt
FROM t_order
GROUP BY user_id, category
ORDER BY cnt DESC;
6. 排序多个字段
先按总金额降序，金额相同再按分类名称升序
SELECT
category,
SUM(amount) AS total_amount
FROM t_order
GROUP BY category
ORDER BY total_amount DESC, category ASC;
7. MySQL 8.0 / PostgreSQL：窗口函数分组排名（RANK / ROW_NUMBER）
如果想要组内排序、组内排名（不是整体排序） 需求：每个分类内部，按金额给订单排名
SELECT
id,
category,
amount,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC) AS rn
FROM t_order;
PARTITION BY：相当于窗口内分组ROW_NUMBER()：不重复序号RANK()：并列同号，序号跳号DENSE_RANK()：并列同号，序号连续
常见坑
GROUP BY字段和 select 非聚合字段不一致（MySQL 5.7 默认严格模式直接报错）混淆
WHERE和HAVING：WHERE 过滤原始行，HAVING 过滤分组后的聚合结果ORDER BY 放 GROUP BY 前面 → 语法错误，顺序：
WHERE → GROUP BY → HAVING → ORDER BY
执行顺序（很重要）
FROM → WHERE → GROUP BY → 聚合计算 → HAVING → SELECT → ORDER BY
本文原创作者：易君召，详见：https://www.yijunzhao.cc/about，转载请注明出处。
原文链接
欢迎访问