如何实现SQL分组后的组内排序排名_利用RANK与DENSE

RANK()和DENSE_RANK()不能在GROUP BY后直接使用，因窗口函数在SELECT阶段执行，需PARTITION BY显式分组，而GROUP BY已折叠行；二者区别在于并列时是否跳名次：RANK()跳空，DENSE_RANK()不跳。

如何实现sql分组后的组内排序排名_利用rank与dense_rank函数

为什么 RANK() 和 DENSE_RANK() 在 GROUP BY 后不能直接用？

因为 SQL 执行顺序是 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY，而窗口函数（如 RANK()）是在 SELECT 阶段计算的，此时分组已经完成，但“组内”这个上下文必须显式定义——你得用 PARTITION BY 指定按哪几列分组，而不是依赖前面的 GROUP BY。换句话说：GROUP BY 会折叠行，而 RANK() 需要原始明细行才能排序。

常见错误现象：SELECT dept, RANK() OVER (ORDER BY salary) FROM emp GROUP BY dept 会报错或结果错乱，因为 salary 不在 GROUP BY 中，且没写 PARTITION BY dept。

正确思路：放弃 GROUP BY 聚合，改用窗口函数的 PARTITION BY 划分逻辑组
若真需要聚合后再排名（比如按部门平均薪资排名），得先用子查询/CTE 算出聚合结果，再在其上套一层窗口函数
RANK() 和 DENSE_RANK() 本身不改变行数，它们只是给每行加一个序号列

用 RANK() 实现组内排序并处理并列情况

RANK() 在遇到相同值时会跳过后续名次。例如 [100, 100, 90] 的排名是 1, 1, 3。

典型场景：查每个部门员工按薪资降序的排名，同薪同名，名次跳空。

SELECT 
  name, dept, salary,
  RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_num
FROM emp;

PARTITION BY dept 定义“组”，等价于按部门分组
ORDER BY salary DESC 决定组内排序方向；升序用 ASC（默认可省略）
如果想按多个字段排序，比如先比 salary，再按 hire_date，写成 ORDER BY salary DESC, hire_date ASC
注意 NULL 默认排在最前（NULLS FIRST），若想排最后，部分数据库支持 NULLS LAST，但 MySQL 8.0+ 和 PostgreSQL 支持，SQLite 不支持

DENSE_RANK() 和 ROW_NUMBER() 的关键区别在哪？

三者都常用于组内排名，但行为差异直接影响业务含义：

ROW_NUMBER()：严格按顺序编号，不考虑值是否重复，[100,100,90] → 1,2,3
RANK()：值相同时名次相同，后续跳空，[100,100,90] → 1,1,3
DENSE_RANK()：值相同时名次相同，后续不跳空，[100,100,90] → 1,1,2

选哪个取决于业务规则。例如“前 3 名获奖”，用 DENSE_RANK() 更合理（避免因并列导致第 4 名实际是第 3 个不同分数）；若需唯一序号做分页或导出 ID，则用 ROW_NUMBER()。

示例：同一部门内，按绩效得分去重排名（相同分算同一档）：

SELECT 
  name, dept, score,
  DENSE_RANK() OVER (PARTITION BY dept ORDER BY score DESC) AS dense_rank_num
FROM emp;

MySQL 8.0+、PostgreSQL、SQL Server 都支持，但 Oracle 有坑

主流数据库基本都支持这三个函数，但 Oracle 用户容易踩一个兼容性坑：Oracle 12c 之前不支持标准窗口函数语法，老写法是 RANK() OVER (ORDER BY ...) 无 PARTITION BY，必须升级到 12c 或更高版本才完整支持 PARTITION BY。

SQL Server 2005+、PostgreSQL 8.4+、MySQL 8.0+、SQLite 3.25+（有限支持）均可用
Spark SQL 和 Flink SQL 也支持，语义一致
性能上，窗口函数一般比自连接或相关子查询快得多，但大数据量时仍建议在 PARTITION BY 和 ORDER BY 字段建联合索引（如 (dept, salary)）
别在视图里盲目套多层窗口函数，某些数据库优化器可能无法下推过滤条件，导致全表扫描

真正容易被忽略的是：窗口函数的结果不能直接用于 WHERE 或 HAVING 过滤（因为执行顺序在后），必须用子查询或 CTE 包裹后才能筛排名，比如“查各部门薪资前 2 名”得写成 SELECT * FROM (SELECT ..., RANK() OVER (...) rnk FROM emp) t WHERE t.rnk 。