使用MAX(startdate)时SQL查询中eb.annualvalue值丢失问题求助

嗨Dan，咱们来拆解你碰到的问题，一步步解决它：

问题根源分析 #

你一开始的查询返回重复行，是因为同一个员工可能有多条bencode为US 401K Plan且未过期（enddate为NULL或晚于2018-01-01）的福利记录，LEFT JOIN后自然会带出多条重复数据。

而你后来加的eb.startdate = (SELECT MAX(eb.startdate) FROM employeebenefit AS [eb])条件，问题出在这个子查询取的是整个employeebenefit表的全局最大startdate，不是每个员工自己的最新startdate。这样只有刚好这条福利记录的startdate等于全局最大值的员工能匹配到，其他人的eb26部分都是NULL，自然annualvalue就丢失了。

两种可行的解决方案 #

方案一：用窗口函数（推荐，性能更优） #

用ROW_NUMBER()窗口函数按员工分组，给每个员工的福利记录按startdate倒序编号，然后只取编号为1的最新记录：

LEFT JOIN (
    SELECT 
        eb.empid, 
        eb.bencode, 
        eb.currencycode AS [currencycode], 
        eb.notes AS [notes], 
        eb.annualvalue,
        -- 按员工ID分组，按startdate从新到旧排序，给每条记录编序号
        ROW_NUMBER() OVER (PARTITION BY eb.empid ORDER BY eb.startdate DESC) AS record_rank
    FROM employeebenefit AS [eb] 
    WHERE eb.bencode IN ('US 401K Plan') 
      AND (eb.enddate IS NULL OR eb.enddate >= '20180101')
) AS eb26 ON eb26.empid = e.empid AND eb26.record_rank = 1 -- 只保留每个员工的最新那条记录

方案二：用关联子查询（兼容老版本数据库） #

如果你的数据库不支持窗口函数，可以先查出每个符合条件的员工的最新startdate，再关联回原表取对应字段：

LEFT JOIN (
    SELECT 
        eb.empid, 
        eb.bencode, 
        eb.currencycode AS [currencycode], 
        eb.notes AS [notes], 
        eb.annualvalue
    FROM employeebenefit AS [eb]
    -- 关联子查询，获取每个员工的最新startdate
    INNER JOIN (
        SELECT empid, MAX(startdate) AS latest_startdate
        FROM employeebenefit
        WHERE bencode IN ('US 401K Plan') 
          AND (enddate IS NULL OR enddate >= '20180101')
        GROUP BY empid
    ) AS latest_eb ON eb.empid = latest_eb.empid AND eb.startdate = latest_eb.latest_startdate
    WHERE eb.bencode IN ('US 401K Plan') 
      AND (eb.enddate IS NULL OR eb.enddate >= '20180101')
) AS eb26 ON eb26.empid = e.empid

小提示 #

执行完查询后，可以先检查每个员工是否最多返回一条福利记录，再确认annualvalue字段是否正常显示，这样就能验证问题是否解决啦～

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