将含ROW_NUMBER()的SQL查询改写为子查询/Top1/MAX函数实现

我需要把下面用ROW_NUMBER()的SQL查询改写成用子查询、TOP 1或MAX函数的形式，但受表数据特性限制一直得不到正确结果，求帮忙。

原SQL查询 #

SELECT * 
FROM
    (SELECT 
         ee.Employee_ID,
         ee.Employer_ID,
         eb.Amount,
         ROW_NUMBER() OVER (PARTITION BY ee.Employer_ID ORDER BY eb.End_Date DESC, ee.Employee_ID DESC) AS ROW_ID -- 按雇主维度分组
     FROM
         Benefit eb
     INNER JOIN 
         Employee ee ON eb.Employee_ID = ee.Employee_ID) a 
WHERE
    ROW_ID = 1 

示例数据 #

Employee表 #

Employee_ID Employer_ID
-----------------------
210100       AC
208584       AC
207599       DC

Benefit表 #

Employee_ID     End_Date    Amount
----------------------------------
210100          25/02/2027  400
208584          25/01/2029  400
207599          25/02/2027  200

预期结果 #

Employer_ID Employee_ID     Amount
-----------------------------------
AC          208584          400
DC          207599          200

我尝试的错误SQL #

SELECT 
    EE.Employee_ID,
    EE.Employer_ID,
    eb.Amount
FROM
    Employee ee
INNER JOIN 
    Benefit EB ON EE.Employee_ID = EB.Employee_ID
WHERE 
    EE.Employee_ID = (SELECT MAX(Employee_ID) AS EMP_ID
                      FROM Employee ee2
                      WHERE EE.Employer_ID = EE2.Employer_ID
                      GROUP BY EE2.Employer_ID)
    AND EB.End_Date = (SELECT MAX(eb2.End_Date) AS END_DATE
                       FROM Benefit eb2
                       WHERE EB.Employee_ID = EB2.Employee_ID
                       GROUP BY EB2.Employee_ID) 
    AND EE.Employer_ID = 'AC'

正确改写方案 #

你的问题出在原尝试的SQL里，把两个独立的MAX条件分开判断了，但原需求是先按End_Date降序，再按Employee_ID降序取每组第一条，不是取最大Employee_ID和各自最大End_Date的交集。以下是两种可行的改写方式：

方式一：用NOT EXISTS关联子查询 #

SELECT 
    ee.Employer_ID,
    ee.Employee_ID,
    eb.Amount
FROM Employee ee
JOIN Benefit eb ON ee.Employee_ID = eb.Employee_ID
WHERE NOT EXISTS (
    SELECT 1
    FROM Employee ee2
    JOIN Benefit eb2 ON ee2.Employee_ID = eb2.Employee_ID
    WHERE ee2.Employer_ID = ee.Employer_ID
    AND (eb2.End_Date > eb.End_Date 
         OR (eb2.End_Date = eb.End_Date AND ee2.Employee_ID > ee.Employee_ID))
)

逻辑：当前记录是该雇主下没有比它End_Date更大，或者End_Date相同但Employee_ID更大的记录，也就是优先级最高的那条。

方式二：用MAX函数组合排序字段 #

SELECT 
    ee.Employer_ID,
    ee.Employee_ID,
    eb.Amount
FROM Employee ee
JOIN Benefit eb ON ee.Employee_ID = eb.Employee_ID
JOIN (
    SELECT 
        Employer_ID,
        MAX(CONVERT(VARCHAR(10), eb2.End_Date, 120) + RIGHT('000000' + CAST(ee2.Employee_ID AS VARCHAR), 6)) AS MaxSortValue
    FROM Employee ee2
    JOIN Benefit eb2 ON ee2.Employee_ID = eb2.Employee_ID
    GROUP BY Employer_ID
) AS max_vals 
ON ee.Employer_ID = max_vals.Employer_ID
AND CONVERT(VARCHAR(10), eb.End_Date, 120) + RIGHT('000000' + CAST(ee.Employee_ID AS VARCHAR), 6) = max_vals.MaxSortValue

逻辑：把End_Date转成标准可排序的字符串格式（如'YYYY-MM-DD'），拼接补零后的Employee_ID，取每个雇主的最大值，再匹配对应的记录，保证排序逻辑和原ROW_NUMBER完全一致。

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