怎么在SQL中根据业务规则给分组后的聚合结果排名-SQL-PHP中文网

row_number() 给分组聚合结果排序，必须先 group by 得到聚合行，再在外层用 row_number() over (order by ...) 对这些聚合行编号；不可在 group by 中直接写 row_number()，也不能在窗口函数中引用未聚合也未分组的字段。

怎么在sql中根据业务规则给分组后的聚合结果排名

用 ROW_NUMBER() 给分组聚合结果排序最直接

分组后排名，本质是先 GROUP BY 得到聚合行，再对这些行按业务规则排序并编号。直接在聚合结果上套一层 ROW_NUMBER() 窗口函数是最稳妥的做法，避免误用 ORDER BY 在 GROUP BY 子句里（那只是控制分组顺序，不产生排名）。

常见错误是写成：SELECT dept, SUM(salary), ROW_NUMBER() OVER (ORDER BY SUM(salary)) FROM emp GROUP BY dept —— 这语法合法但逻辑错：ROW_NUMBER() 会作用于整个结果集，不是“每个部门内排名”，也没体现“按业务规则”。必须明确 PARTITION BY 和 ORDER BY 的组合意图。

如果要“每个部门内按总薪资降序排位”，ROW_NUMBER() OVER (PARTITION BY dept ORDER BY SUM(salary) DESC)
如果要“全公司所有部门按平均绩效分升序统一排位”，就去掉 PARTITION BY，只留 ORDER BY AVG(score)
RANK() 和 DENSE_RANK() 适合处理并列场景，比如两个部门总薪资相同，是否要占掉下一个名次（RANK()）还是紧挨着排（DENSE_RANK()）

聚合字段不能直接出现在窗口函数的 ORDER BY 里？

能，但必须确保它属于聚合上下文——也就是该字段要么是 GROUP BY 列，要么是聚合函数结果（如 SUM()、AVG()）。否则会报错 column "xxx" must appear in the GROUP BY clause or be used in an aggregate function。

典型翻车点：想按某个非分组、非聚合字段排序，比如 SELECT dept, COUNT(*) FROM emp GROUP BY dept ORDER BY hire_date —— hire_date 既没被分组也没被聚合，数据库不知道选哪一行的值。修复方式只有两个：把它加进 GROUP BY（变成按部门+入职日期分组），或改用聚合表达式（如 MIN(hire_date) 或 MAX(hire_date)）。

正确示例：SELECT dept, COUNT(*), ROW_NUMBER() OVER (ORDER BY MIN(hire_date)) FROM emp GROUP BY dept
错误示例：ROW_NUMBER() OVER (ORDER BY hire_date)（未聚合也未分组）
别依赖 SELECT 列别名在窗口函数中使用；部分数据库（如 PostgreSQL）不支持，老老实实写原表达式

MySQL 8.0+ 和旧版兼容性差异明显

MySQL 5.7 及更早版本不支持窗口函数，强行用 ROW_NUMBER() 会报错 FUNCTION xxx.ROW_NUMBER does not exist。必须升级到 8.0+，且确认 sql_mode 没禁用窗口函数（极少见，但自定义配置可能关掉）。

替代方案（仅限旧版 MySQL）只能靠变量模拟，但极易出错：变量执行顺序不保证、JOIN 伪表易膨胀、空组处理麻烦。例如用 @rank := @rank + 1 配合子查询排序，一旦数据量大或并发读，排名就错乱。

检查版本：SELECT VERSION();，输出带 8.0 才能用窗口函数
确认支持：SELECT ROW_NUMBER() OVER () FROM dual; 能跑通即 ok
别在视图或存储过程中混用新旧写法，MySQL 8.0 视图里用了窗口函数，低版本客户端连上去会直接报错

业务规则复杂时，先算聚合再排名比嵌套窗口更可控

当排名依据不止一个字段，比如“先按部门总业绩降序，业绩相同时按平均响应时长升序”，或者需要过滤掉某些分组（如只排业绩 > 100 万的部门），硬塞进单条 SQL 容易让逻辑缠绕不清。

更清晰的做法是：用 CTE 或子查询先把聚合结果算出来，再在外面套一层排名。这样每步职责单一，调试、加 WHERE、补字段都方便。

WITH dept_agg AS (
  SELECT 
    dept,
    SUM(revenue) AS total_rev,
    AVG(response_time) AS avg_rt
  FROM sales
  GROUP BY dept
  HAVING SUM(revenue) > 1000000
)
SELECT 
  dept,
  total_rev,
  avg_rt,
  ROW_NUMBER() OVER (ORDER BY total_rev DESC, avg_rt ASC) AS rank_by_rule
FROM dept_agg;

嵌套写法（如把 HAVING 条件挪进窗口函数的 FILTER）不仅可读性差，PostgreSQL 的 FILTER 也不支持在 OVER 子句里用，MySQL 根本没这语法。一步到位看似简洁，实际埋坑。

真正容易被忽略的是：业务规则里的“并列是否同名次”“跳过空值”“分区边界是否包含 NULL”——这些细节不提前和产品对齐，上线后才发现排名和预期不符，就得翻查每个 RANK() 和 NULLS FIRST/LAST 的行为。