如何用SQL Server编写SQL语句获取符合特定条件的最新记录

测试数据 #

需求 #

• 针对每个contract no.，若存在estate = 'LR'的记录，则获取该记录build date之前最新的记录；若有多个记录build date相同，优先选择type = 'X'的记录。
• 若某个contract no.不存在estate = 'LR'的记录，则选择build date最新的记录；若有多个记录build date相同，优先选择type = 'X'的记录。

预期输出 #

SQL语句（适用于SQL Server） #

WITH ContractLR AS (
    -- 统计每个合同号对应的LR记录build date，无LR则用最大日期值标记
    SELECT 
        [contract no.],
        MAX(CASE WHEN estate = 'LR' THEN [build date] ELSE 99991231 END) AS lr_build_date
    FROM YourTableName
    GROUP BY [contract no.]
),
RankedRecords AS (
    SELECT 
        t.*,
        ROW_NUMBER() OVER (
            PARTITION BY t.[contract no.]
            ORDER BY 
                -- 有LR的合同优先排序小于LR日期的记录，无LR则按最新日期排序
                CASE WHEN t.[build date] < cl.lr_build_date THEN t.[build date] ELSE 0 END DESC,
                -- 同日期下优先选取type为X的记录
                CASE WHEN t.type = 'X' THEN 1 ELSE 0 END DESC,
                -- 同日期同类型时用id兜底避免重复
                t.id DESC
        ) AS rn
    FROM YourTableName t
    JOIN ContractLR cl ON t.[contract no.] = cl.[contract no.]
    -- 过滤规则：有LR则只保留日期早于LR的记录，无LR则保留所有
    WHERE (cl.lr_build_date != 99991231 AND t.[build date] < cl.lr_build_date) 
        OR cl.lr_build_date = 99991231
)
SELECT id, [contract no.], estate, type, [build date], remarks
FROM RankedRecords
WHERE rn = 1;

说明 #

1. ContractLR公共表表达式：先为每个合同号匹配对应的LR记录日期，无LR的合同用99991231作为识别标记。
2. RankedRecords公共表表达式：通过ROW_NUMBER()按合同号分区排序，严格遵循需求中的优先级规则为每条记录排名。
3. 最终筛选出每个分区排名第一的记录，即为符合要求的结果。

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