SQL Server中基于优先级从两张表筛选数据的查询方法

需求回顾 #

需要为每个UserNo筛选唯一的(Subject, UserNo)记录，优先级规则：

• 优先选择Table A中highest_Rate最高的记录
• 若同一UserNo下多条记录在Table A的评分相同，则选择对应记录在Table B中highest_Rate最高的那条
  最终结果按Subject、UserNo排序。

解决方案：使用窗口函数ROW_NUMBER() #

在SQL Server中，ROW_NUMBER()窗口函数可以精准实现按分组排序取Top1的需求，具体语句如下：

WITH RankedRecords AS (
    SELECT 
        A.Subject,
        A.UserNo,
        -- 按UserNo分组，先按Table A评分降序，再按Table B评分降序生成行号
        ROW_NUMBER() OVER (
            PARTITION BY A.UserNo 
            ORDER BY A.highest_Rate DESC, B.highest_Rate DESC
        ) AS rn
    FROM TableA A
    INNER JOIN TableB B 
        ON A.Subject = B.Subject AND A.UserNo = B.UserNo
)
SELECT Subject, UserNo
FROM RankedRecords
WHERE rn = 1  -- 筛选每个UserNo优先级最高的记录
ORDER BY Subject, UserNo;

代码解释 #

1. CTE（公共表表达式）RankedRecords：

   • 关联Table A和Table B，确保每条(Subject, UserNo)对应两个表的评分数据
   • PARTITION BY A.UserNo：将数据按UserNo分组，每个用户组单独计算排序序号
   • ORDER BY A.highest_Rate DESC, B.highest_Rate DESC：组内先按Table A的评分从高到低排序，评分相同时再按Table B的评分从高到低排序
   • rn字段为每个用户组内的排序序号，序号为1的就是该用户符合优先级要求的记录

2. 主查询：

   • 筛选rn = 1的记录，得到每个用户唯一的目标(Subject, UserNo)
   • 最后按Subject、UserNo排序，匹配预期结果格式

对您尝试代码的补充 #

您之前的代码仅完成了两个表的内连接，没有实现按UserNo去重和优先级筛选的核心逻辑。通过添加窗口函数的分组排序规则，就能精准满足需求中的筛选要求。

内容的提问来源于stack exchange，提问作者ally