SQL Server 2016中基于日期匹配或最新ID的表左连接需求

针对你提出的左连接+优先级筛选的SQL需求，我们可以借助窗口函数来实现，这种方式在SQL Server 2016中完全兼容，逻辑清晰且执行效率较高。

需求回顾 #

• 左连接Table1与Table2，关联条件为PatientID
• 优先筛选GETDATE()处于FromDate和ToDate区间内的Table2记录
• 若该患者无符合时间条件的记录，则取其ID最大的Table2记录

表结构与示例数据 #

Table1

Table2

实现代码 #

WITH RankedRecords AS (
    SELECT 
        t1.PatientID,
        t2.ID,
        t2.FromDate,
        t2.ToDate,
        -- 定义排序优先级：先选时间符合的记录，再按ID降序取最新
        ROW_NUMBER() OVER (
            PARTITION BY t1.PatientID 
            ORDER BY 
                CASE WHEN GETDATE() BETWEEN t2.FromDate AND t2.ToDate THEN 0 ELSE 1 END,
                t2.ID DESC
        ) AS RowNum
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.PatientID = t2.PatientID
)
SELECT 
    PatientID,
    FromDate,
    ToDate
FROM RankedRecords
WHERE RowNum = 1;

代码解释 #

1. CTE RankedRecords：通过公共表表达式给每个患者的关联记录标记排序号
   • PARTITION BY t1.PatientID：按患者ID分组，确保每个患者的记录单独处理
   • ORDER BY子句：
     • 第一个排序条件用CASE语句：如果当前日期在时间区间内，赋值为0，否则为1，这样符合时间条件的记录会排在最前面
     • 第二个排序条件是t2.ID DESC：当没有符合时间条件的记录时，ID最大的记录会排在第一位
2. 最终筛选：只保留每个分组中RowNum=1的记录，也就是我们需要的优先级最高的那条

注意事项 #

如果某个患者在Table2中没有任何关联记录，查询结果里的FromDate和ToDate会返回NULL，这符合左连接的预期行为。

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