如何用DENSE_RANK函数实现SQL查询中每组前三名的长尾词需求？

本文共计843个文字，预计阅读时间需要4分钟。

由于需要处理并列情况，如果第2、3名分数相同，则使用DENSE_RANK()函数将它们都标记为2，下一名标记为3；而使用ROW_NUMBER()函数则强制编号为2和3，导致实际取4条记录。业务上常说的前三名通常允许并列，如得分前3名的用户，而不是任意3个用户。

常见错误现象：ROW_NUMBER() OVER (PARTITION BY dept ORDER BY score DESC) 在有并列时漏掉同分者，或误取超过3条。

• DENSE_RANK() 编号连续、不跳过：1,2,2,3,3,4…
• RANK() 会跳号：1,2,2,4,4,6…（中间缺3/5）——不适合“取前三”语义
• 必须配合子查询或CTE，不能直接在WHERE中引用窗口函数结果

标准写法：用CTE + DENSE_RANK() + 过滤

窗口函数不能出现在WHERE子句里，所以得先算排名，再过滤。CTE最清晰，也兼容绝大多数主流数据库（PostgreSQL、SQL Server、Oracle、MySQL 8.0+）。

WITH ranked AS ( SELECT name, dept, score, DENSE_RANK() OVER (PARTITION BY dept ORDER BY score DESC) AS drk FROM employees ) SELECT name, dept, score FROM ranked WHERE drk <= 3;

注意点：

• PARTITION BY dept 决定“每组”的划分依据，换成category或region等字段即可适配其他场景
• ORDER BY score DESC 必须明确方向，升序会变成“最低分前三”
• 如果score为NULL，默认排在最前（因DESC），需加NULLS LAST（PostgreSQL/Oracle）或用COALESCE(score, -999999)兜底

MySQL 5.7 或旧版PostgreSQL怎么绕过DENSE_RANK()

这些版本不支持窗口函数，只能用自连接或相关子查询模拟排名，性能差、逻辑绕。但真要兼容，推荐相关子查询写法（比自连接更易读）：

SELECT e1.name, e1.dept, e1.score FROM employees e1 WHERE ( SELECT COUNT(DISTINCT e2.score) FROM employees e2 WHERE e2.dept = e1.dept AND e2.score >= e1.score ) <= 3;

说明：

• 用COUNT(DISTINCT e2.score) 实现“密集排名”逻辑：相同分数只算一次
• e2.score >= e1.score 是关键，确保统计的是“不低于当前行的分数个数”
• 性能隐患：数据量超几千行时明显变慢，且无法利用索引优化排名计算
• 不推荐在新项目中用此方案，升级数据库或改用应用层分页更稳妥

容易被忽略的排序稳定性问题

当score完全相同时，DENSE_RANK() 会给所有行相同排名，但最终结果集顺序不确定——数据库可能每次返回不同行序。这在分页或导出时造成困惑。

解决方法：在ORDER BY中追加唯一字段（如主键或时间戳）保证稳定：

DENSE_RANK() OVER ( PARTITION BY dept ORDER BY score DESC, id ASC ) AS drk

否则可能出现：两次查询都返回drk≤3的记录，但具体哪3条人不一致。

另外，TOP 3 WITH TIES（SQL Server）或LIMIT … WITH TIES（PostgreSQL 13+）能替代部分场景，但仅限单组全局排序，不支持PARTITION BY——所以还是得靠DENSE_RANK()。