如何用子查询按多条件去重：先取最新日期再筛选ROLE='BLUE'

现有SQL已经实现了按ID分组提取最新DATE的去重逻辑，要添加第二步筛选（针对取最新日期后仍存在的重复项，保留ROLE为'BLUE'的记录），可以通过以下两种方式修改：

方式一：基于原有代码添加过滤条件 #

直接在主查询中加入WHERE子句，筛选ROLE为'BLUE'的记录：

SELECT a.ID,
       a.STATUS,
       a.ROLE,
       a.DATE
FROM
(
    SELECT ID, Max(DATE) as MaxDate
    FROM WorkersTest
    GROUP BY ID
) b
INNER JOIN WorkersTest as a
ON a.ID = b.ID
AND a.DATE = b.MaxDate
WHERE a.ROLE = 'BLUE' -- 新增：筛选ROLE为BLUE的记录
ORDER BY b.ID

逻辑说明：

• 先通过子查询b按ID分组得到每个ID的最新日期MaxDate
• 关联原表获取对应ID和最新日期的完整记录
• 最后过滤掉ROLE不为'BLUE'的条目，若某个ID的最新日期对应的所有记录中没有ROLE='BLUE'的，该ID会被排除

方式二：用窗口函数简化逻辑（更高效直观） #

使用ROW_NUMBER()窗口函数可以避免JOIN操作，直接完成“取最新日期+筛选BLUE角色”的逻辑：

SELECT ID, STATUS, ROLE, DATE
FROM (
    SELECT 
        ID, STATUS, ROLE, DATE,
        -- 按ID分组，按日期降序排序，标记每条记录的序号（最新日期为1）
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE DESC) AS rn
    FROM WorkersTest
) t
WHERE rn = 1 AND ROLE = 'BLUE' -- 取最新日期的记录，且ROLE为BLUE
ORDER BY ID

逻辑说明：

• 内层子查询给每个ID的记录按日期从新到旧排序，最新的记录序号为rn=1
• 外层直接筛选序号为1（最新日期）且ROLE为'BLUE'的记录，逻辑更简洁，性能也更优

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