SQL实现按最新audit-date排序并展示去重WB_ID的方法

需求说明：从[dbo].[Entity]表中获取去重的WB_ID，同时依据每个WB_ID对应的最新audit-date进行倒序排列，且保留原查询中的所有筛选条件。

修改后的SQL查询 #

SELECT [WB_ID]
FROM [dbo].[Entity]
WHERE 
(
    1 = CASE  
        WHEN audit_user_id LIKE '%' + '' + '%' THEN 1
        WHEN '' = '' OR '' IS NULL THEN 1
        ELSE 0
    END  

    AND  
    1 = CASE  
        WHEN CONVERT(date, audit_date) BETWEEN CONVERT(date, '') AND CONVERT(date, '') THEN 1
        WHEN '' = '' OR '' IS NULL THEN 1
        ELSE 0  
    END

    AND  1 = CASE  
        WHEN audit_mode = '' THEN 1
        WHEN '' = '' OR '' IS NULL OR '' = 'All' THEN 1
        ELSE 0  
    END
)
GROUP BY [WB_ID]
ORDER BY MAX(audit_date) DESC;

关键说明 #

• 用GROUP BY [WB_ID]替代DISTINCT，既能实现WB_ID去重，又能通过聚合函数MAX(audit_date)获取每个WB_ID对应的最新审计日期。
• 排序时直接基于MAX(audit_date)倒序，确保结果按每个WB_ID的最新记录时间排序。
• 完整保留了原查询中的所有筛选逻辑，保证数据范围符合业务要求。

如果需要同时展示每个WB_ID的最新audit_date，可以使用以下查询：

SELECT [WB_ID], MAX(audit_date) AS latest_audit_date
FROM [dbo].[Entity]
WHERE 
(
    1 = CASE  
        WHEN audit_user_id LIKE '%' + '' + '%' THEN 1
        WHEN '' = '' OR '' IS NULL THEN 1
        ELSE 0
    END  

    AND  
    1 = CASE  
        WHEN CONVERT(date, audit_date) BETWEEN CONVERT(date, '') AND CONVERT(date, '') THEN 1
        WHEN '' = '' OR '' IS NULL THEN 1
        ELSE 0  
    END

    AND  1 = CASE  
        WHEN audit_mode = '' THEN 1
        WHEN '' = '' OR '' IS NULL OR '' = 'All' THEN 1
        ELSE 0  
    END
)
GROUP BY [WB_ID]
ORDER BY latest_audit_date DESC;

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