如何用ROW_NUMBER替代MAX()实现员工母语与熟练语言查询

我来帮你把原来的查询改成用ROW_NUMBER()函数实现的版本，先拆解下原查询的核心逻辑：它会为每个员工提取出母语（对应LanguageLevelId = 4）和流利掌握的语言（对应LanguageLevelId = 2），最后按员工ID聚合，得到每个员工一行的结果（如果有多个同级别语言，会取标签的最大值）。

用ROW_NUMBER()的思路是先给每个员工同一语言级别的记录排序，筛选出我们需要的那条（对应原查询MAX取到的结果），再通过条件聚合或者关联来得到最终结果。下面提供两种可行的写法：

写法一：用CTE拆分筛选后关联 #

这种写法更直观，把母语和流利语言的筛选逻辑分开，再合并结果：

WITH LanguageRankings AS (
    SELECT 
        al.AdminFileId,
        l.Label,
        al.LanguageLevelId,
        -- 为母语级别（4）的记录排序，优先保留标签最大的那条（和原查询MAX逻辑一致）
        ROW_NUMBER() OVER (
            PARTITION BY al.AdminFileId 
            ORDER BY CASE WHEN al.LanguageLevelId = 4 THEN 0 ELSE 1 END, l.Label DESC
        ) AS rn_mother,
        -- 为流利级别（2）的记录排序，同样保留标签最大的那条
        ROW_NUMBER() OVER (
            PARTITION BY al.AdminFileId 
            ORDER BY CASE WHEN al.LanguageLevelId = 2 THEN 0 ELSE 1 END, l.Label DESC
        ) AS rn_fluent
    FROM AF_Language al
    LEFT JOIN AF_AdminFile aaf ON aaf.AdminFileId = al.AdminFileId
    INNER JOIN Employee e ON e.AdminFileId = aaf.AdminFileId
    LEFT JOIN Language l ON al.LanguageId = l.ID
),
MotherTongue AS (
    SELECT AdminFileId, Label AS MotherTongue
    FROM LanguageRankings
    WHERE LanguageLevelId = 4 AND rn_mother = 1
),
FluentLanguages AS (
    SELECT AdminFileId, Label AS Fluent
    FROM LanguageRankings
    WHERE LanguageLevelId = 2 AND rn_fluent = 1
)
SELECT 
    COALESCE(m.AdminFileId, f.AdminFileId) AS AdminFileId,
    m.MotherTongue,
    f.Fluent
FROM MotherTongue m
FULL OUTER JOIN FluentLanguages f 
    ON m.AdminFileId = f.AdminFileId
ORDER BY AdminFileId;

逻辑说明：

1. 在LanguageRankings CTE中，我们为每个员工（按AdminFileId分区）生成两个行号：
   • rn_mother：先把母语级别的记录排在前面，再按标签降序排序，确保每个员工的母语行中，标签最大的那条行号为1。
   • rn_fluent：同理处理流利级别的记录。
2. 用两个子CTE分别筛选出每个员工的母语和流利语言行。
3. 最后用FULL OUTER JOIN合并结果，确保即使员工只有母语或只有流利语言，也能被包含在结果中（和原查询的LEFT JOIN逻辑对齐）。

写法二：用单个CTE结合条件聚合 #

这种写法更紧凑，直接在聚合时筛选出排序后的目标行：

WITH RankedLanguages AS (
    SELECT 
        al.AdminFileId,
        l.Label,
        al.LanguageLevelId,
        -- 按员工+语言级别分区，标签降序排序，确保取到最大的标签（和原查询MAX一致）
        ROW_NUMBER() OVER (
            PARTITION BY al.AdminFileId, al.LanguageLevelId 
            ORDER BY l.Label DESC
        ) AS rn
    FROM AF_Language al
    LEFT JOIN AF_AdminFile aaf ON aaf.AdminFileId = al.AdminFileId
    INNER JOIN Employee e ON e.AdminFileId = aaf.AdminFileId
    LEFT JOIN Language l ON al.LanguageId = l.ID
)
SELECT 
    AdminFileId,
    -- 筛选出母语级别且行号为1的记录（即标签最大的母语）
    MAX(CASE WHEN LanguageLevelId = 4 AND rn = 1 THEN Label END) AS MotherTongue,
    -- 筛选出流利级别且行号为1的记录（即标签最大的流利语言）
    MAX(CASE WHEN LanguageLevelId = 2 AND rn = 1 THEN Label END) AS Fluent
FROM RankedLanguages
GROUP BY AdminFileId;

逻辑说明：

1. 在RankedLanguages CTE中，按AdminFileId和LanguageLevelId分区，给同一员工同一级别的语言记录按标签降序排序，行号为1的就是标签最大的那条（对应原查询中MAX取到的结果）。
2. 之后通过条件聚合，分别提取出每个员工的母语和流利语言，最终得到和原查询完全一致的输出。

这两种写法都能得到你想要的结果，你可以根据自己的习惯选择其中一种。

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