SQL中如何实现排名后的关联查询？结合ROW_NUMBER与JOIN进行筛选的方法详解

在数据处理过程中，常常需要先对记录进行分组排名，再基于排名结果去关联其他表获取补充信息。使用ROW_NUMBER窗口函数生成排名，再将其作为子查询或公用表表达式（CTE）与维表进行JOIN，是一种通用且易维护的做法。

一、基本思路

核心步骤分为两步：第一步用ROW_NUMBER() OVER (PARTITION BY 分组列 ORDER BY 排序列) 为每一组数据标明序号；第二步将带序号的结果集与需要关联的表使用JOIN连接，并在连接条件或WHERE中筛选目标排名。

1.1 使用派生表

把排名查询放在FROM后的括号里，形成派生表，再JOIN部门表：

SELECT
  e.emp_id,
  e.emp_name,
  e.salary,
  d.dept_name
FROM (
  SELECT
    emp_id,
    emp_name,
    dept_id,
    salary,
    ROW_NUMBER() OVER (
      PARTITION BY dept_id
      ORDER BY salary DESC
    ) AS rn
  FROM employee
) e
JOIN department d
  ON e.dept_id = d.dept_id
WHERE e.rn = 1;

1.2 使用CTE写法

CTE能让逻辑更清晰，效果与派生表相同：

WITH ranked_emp AS (
  SELECT
    emp_id,
    emp_name,
    dept_id,
    salary,
    ROW_NUMBER() OVER (
      PARTITION BY dept_id
      ORDER BY salary DESC
    ) AS rn
  FROM employee
)
SELECT
  r.emp_id,
  r.emp_name,
  r.salary,
  d.dept_name
FROM ranked_emp r
JOIN department d
  ON r.dept_id = d.dept_id
WHERE r.rn = 1;

二、带条件的关联筛选

有时不仅取排名第一，还要关联另一张表做存在性判断。例如只保留有考勤记录的员工：

WITH ranked_emp AS (
  SELECT
    emp_id,
    dept_id,
    salary,
    ROW_NUMBER() OVER (
      PARTITION BY dept_id
      ORDER BY salary DESC
    ) AS rn
  FROM employee
)
SELECT r.emp_id, d.dept_name, r.salary
FROM ranked_emp r
JOIN department d ON r.dept_id = d.dept_id
JOIN attendance a ON r.emp_id = a.emp_id
WHERE r.rn <= 3
  AND a.month = '2024-03';

三、注意事项

• ROW_NUMBER在并列排序时会任意分配序号，若需并列同名次应使用RANK或DENSE_RANK。
• 分区列和排序列建议建立索引，避免大表全量排序导致性能下降。
• JOIN前先过滤排名，能减少参与连接的数据量，提升效率。

排名后的关联查询本质是先投影出带序号的集合，再把它当成普通表使用，因此任何合法的JOIN语法都可叠加。

四、小结

通过ROW_NUMBER与JOIN结合，可以优雅地解决先排名后关联的问题。把排名逻辑封装在子查询或CTE中，外层直接JOIN并加筛选，代码可读性高，也方便后续扩展条件。

SQLROW_NUMBERJOIN修改时间：2026-07-27 14:36:18