如何按State优先级及最新日期筛选获取数据表中的Id字段？

问题描述 #

现有数据表结构及示例数据如下：

需按以下规则筛选记录并提取对应的Id字段：

• State优先级：A > P > V > T > 其他
• 同优先级下，取Date1最新的记录；若Date1相同，取Date2最新的记录
• 最终用筛选出的Id查询另一张表是否存在对应记录

此前尝试StackOverflow推荐的CTE方案未解决问题。

解决方案 #

通过给State分配优先级权重，结合窗口函数ROW_NUMBER()可实现精准筛选，以下是适用于SQL Server的示例代码：

WITH RankedRecords AS (
    SELECT 
        Id,
        -- 按State优先级、Date1倒序、Date2倒序排序生成行号
        ROW_NUMBER() OVER (
            PARTITION BY Id 
            ORDER BY 
                CASE State 
                    WHEN 'A' THEN 1 
                    WHEN 'P' THEN 2 
                    WHEN 'V' THEN 3 
                    WHEN 'T' THEN 4 
                    ELSE 5 
                END ASC,
                Date1 DESC,
                Date2 DESC
        ) AS RowNum
    FROM YourTableName -- 替换为你的实际表名
)
SELECT Id
FROM RankedRecords
WHERE RowNum = 1;

代码说明 #

1. CTE分组排序：通过CASE语句给不同State分配优先级数值（数值越小优先级越高），再按Id分组，每组内按优先级、Date1、Date2排序生成行号
2. 筛选最优记录：取每组内行号为1的记录，即为每个Id符合规则的最优记录
3. 关联验证存在性：将上述结果作为子查询，关联另一张表验证Id是否存在，示例：

SELECT r.Id, 
       CASE WHEN EXISTS (SELECT 1 FROM AnotherTable a WHERE a.Id = r.Id) 
            THEN '存在' 
            ELSE '不存在' 
       END AS ExistsInAnotherTable
FROM (
    WITH RankedRecords AS (
        SELECT 
            Id,
            ROW_NUMBER() OVER (
                PARTITION BY Id 
                ORDER BY 
                    CASE State 
                        WHEN 'A' THEN 1 
                        WHEN 'P' THEN 2 
                        WHEN 'V' THEN 3 
                        WHEN 'T' THEN 4 
                        ELSE 5 
                    END ASC,
                    Date1 DESC,
                    Date2 DESC
            ) AS RowNum
        FROM YourTableName
    )
    SELECT Id FROM RankedRecords WHERE RowNum = 1
) r;

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