使用SQL MAXDATE提取HR表数据行：去重最新生效日期问题求助

我来帮你搞定这个问题！你现在的需求是从HR表里拉取共享同一Primary_Key_Value的员工和家属数据，但因为部分家属有多个MED_COV_EFFECTIVE_DATE导致重复，想用MAX函数取最新日期却报错，对吧？我给你梳理下解决思路和具体的SQL写法：

先搞清楚出错的大概率原因 #

你之前用MAX(MED_COV_EFFECTIVE_DATE)报错，十有八九是分组逻辑没写对：当使用聚合函数（比如MAX）时，SELECT里的非聚合字段必须出现在GROUP BY子句里（不同数据库的严格模式要求不同，比如MySQL的ONLY_FULL_GROUP_BY），另外如果不小心把员工的记录也加入了聚合分组，还可能导致员工数据丢失或者错误合并。

方案一：用子查询/CTE先获取最新日期，再关联原表 #

这种方法兼容性好，几乎所有数据库都支持：

-- 第一步：先找出每个家属的最新生效日期
WITH LatestCovDates AS (
    SELECT 
        Primary_Key_Value,
        DEPENDENT_ID, -- 这里假设DEPENDENT_ID是每个家属的唯一标识，没有的话换成能区分家属的字段
        MAX(MED_COV_EFFECTIVE_DATE) AS Latest_Effective_Date
    FROM HR_TABLE
    WHERE RELATIONSHIP != 'EMPLOYEE' -- 只针对家属做日期筛选
    GROUP BY Primary_Key_Value, DEPENDENT_ID
)
-- 第二步：关联原表，获取完整的员工+最新家属数据
SELECT h.*
FROM HR_TABLE h
-- 关联家属的最新日期记录
LEFT JOIN LatestCovDates lcd 
    ON h.Primary_Key_Value = lcd.Primary_Key_Value
    AND h.DEPENDENT_ID = lcd.DEPENDENT_ID
    AND h.MED_COV_EFFECTIVE_DATE = lcd.Latest_Effective_Date
-- 过滤条件：保留所有员工记录，只保留家属的最新日期记录
WHERE 
    h.RELATIONSHIP = 'EMPLOYEE'
    OR (h.RELATIONSHIP != 'EMPLOYEE' AND lcd.Latest_Effective_Date IS NOT NULL)

方案二：用窗口函数（更简洁，适合支持窗口函数的数据库） #

如果你的数据库是MySQL 8+、PostgreSQL、SQL Server这类支持窗口函数的，用ROW_NUMBER()会更直观：

WITH RankedRecords AS (
    SELECT 
        *,
        -- 按Primary_Key_Value和家属标识分组，按生效日期倒序排名，最新的排第1
        ROW_NUMBER() OVER (
            PARTITION BY Primary_Key_Value, DEPENDENT_ID 
            ORDER BY MED_COV_EFFECTIVE_DATE DESC
        ) AS rn
    FROM HR_TABLE
)
SELECT *
FROM RankedRecords
WHERE 
    -- 所有员工记录直接保留
    RELATIONSHIP = 'EMPLOYEE'
    -- 家属只保留排名第一的（也就是最新生效日期的那条）
    OR (RELATIONSHIP != 'EMPLOYEE' AND rn = 1)

注意事项 #

• 如果你的表中没有DEPENDENT_ID这种家属唯一标识，就换成能区分同一家属不同记录的字段，比如FULL_NAME+RELATIONSHIP（但尽量用唯一ID更准确）
• 如果员工本身也存在多个MED_COV_EFFECTIVE_DATE，你可以把员工也加入窗口函数的分组里，同样取rn=1的最新记录

这样处理后，就能完美解决重复数据的问题，只保留每个家属的最新生效日期记录啦！

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