SQL分组后排序的常见场景
在业务查询中，我们经常会遇到需要先按某个字段分组，再在每个分组内按其他字段排序的需求。比如统计每个部门的员工薪资排名，或者获取每个分类下最新的商品信息等，这类需求都需要用到分组后排序的技巧。
使用窗口函数实现分组排序
窗口函数是处理分组后排序最便捷的方式，它可以在不改变原表行数的情况下，为每个分组内的行计算排序值。常用的窗口排序函数有ROW_NUMBER()、RANK()和DENSE_RANK()，三者的区别在于对相同值的排序处理不同。
以下是一个按部门分组，统计每个部门员工薪资排名的示例：
-- 创建测试表
CREATE TABLE employee (
id INT,
dept_name VARCHAR(50),
emp_name VARCHAR(50),
salary DECIMAL(10,2)
);
-- 插入测试数据
INSERT INTO employee VALUES
(1, '技术部', '张三', 12000),
(2, '技术部', '李四', 15000),
(3, '技术部', '王五', 12000),
(4, '产品部', '赵六', 11000),
(5, '产品部', '孙七', 13000);
-- 使用ROW_NUMBER实现分组内排序，相同薪资按id升序排列
SELECT
dept_name,
emp_name,
salary,
ROW_NUMBER() OVER (PARTITION BY dept_name ORDER BY salary DESC, id ASC) AS row_num,
RANK() OVER (PARTITION BY dept_name ORDER BY salary DESC) AS rank_num,
DENSE_RANK() OVER (PARTITION BY dept_name ORDER BY salary DESC) AS dense_rank_num
FROM employee;
上述查询中，PARTITION BY dept_name表示按部门分组，ORDER BY salary DESC, id ASC表示每个分组内先按薪资降序排列，薪资相同则按id升序排列。执行后可以得到每个部门内员工的排序结果。
使用子查询结合GROUP BY实现分组后取排序值
如果使用不支持窗口函数的低版本数据库，也可以通过子查询结合GROUP BY的方式实现分组后排序。这种方式的逻辑是先对每个分组内的数据进行排序，再通过关联查询获取完整的分组排序结果。
以下是获取每个部门薪资最高的前两名员工的示例：
-- 子查询先按部门分组排序，再关联获取完整信息
SELECT
t.dept_name,
t.emp_name,
t.salary
FROM (
SELECT
e1.dept_name,
e1.emp_name,
e1.salary,
(SELECT COUNT(*)
FROM employee e2
WHERE e2.dept_name = e1.dept_name
AND (e2.salary > e1.salary OR (e2.salary = e1.salary AND e2.id <= e1.id))
) AS rank_num
FROM employee e1
) t
WHERE t.rank_num <= 2
ORDER BY t.dept_name, t.salary DESC;
子查询中通过关联同表的方式计算每个员工在部门内的排名，外层查询筛选出排名前两名的记录，最终得到每个部门薪资最高的两位员工信息。
不同分组排序方式的对比
两种方式的特点可以通过下表对比：
| 实现方式 | 适用场景 | 优点 | 缺点 |
|---|---|---|---|
| 窗口函数 | 支持窗口函数的数据库版本 | 语法简洁，性能较好，逻辑清晰 | 低版本数据库不支持 |
| 子查询结合GROUP BY | 所有SQL数据库 | 兼容性好，所有版本都支持 | 语法复杂，数据量大时性能较差 |
分组排序的注意事项
- 使用窗口函数时，
PARTITION BY后面的字段是分组依据，ORDER BY后面的字段是分组内的排序依据，排序字段可以包含多个。 - 如果分组内有相同排序值的行，需要根据业务需求选择
ROW_NUMBER、RANK还是DENSE_RANK，避免排序结果不符合预期。 - 使用子查询实现时，如果排序字段有相同值，需要增加额外的判断条件（比如id）来保证排序的唯一性，否则可能出现排名计算错误。
分组后排序的核心是先明确分组维度，再确定分组内的排序规则，根据数据库版本选择合适的实现方式即可。