如何用SQL获取指定Identifier前后的日期关联记录

方法：用窗口函数获取基准日期范围 #

可以通过PARTITION BY UniqueID窗口函数，先提取每个用户（UniqueID）对应的identifier=1的入院和出院日期，再基于这个基准筛选符合条件的记录。

WITH base_dates AS (
    SELECT
        UniqueID,
        MAX(CASE WHEN Identifier = 1 THEN AdmissionDate END) OVER (PARTITION BY UniqueID) AS ref_admission,
        MAX(CASE WHEN Identifier = 1 THEN ReleaseDate END) OVER (PARTITION BY UniqueID) AS ref_release
    FROM your_table_name
)
SELECT
    t.UniqueID,
    t.AdmissionDate,
    t.ReleaseDate,
    t.Identifier
FROM your_table_name t
JOIN base_dates bd ON t.UniqueID = bd.UniqueID
WHERE
    t.Identifier = 1
    OR (
        t.Identifier = 2
        AND (
            t.ReleaseDate < bd.ref_admission
            OR t.AdmissionDate > bd.ref_release
        )
    )
ORDER BY t.UniqueID, t.AdmissionDate DESC;

逻辑说明 #

1. CTE base_dates：按UniqueID分区，用MAX(CASE...)提取每个用户对应的identifier=1的入院（ref_admission）和出院（ref_release）日期——如果每个用户的identifier=1记录唯一，这个方法直接生效；若存在多条identifier=1的记录，MAX会取最新的日期，可根据实际需求调整聚合方式。
2. 主查询筛选：
   • 直接保留所有identifier=1的记录
   • 对identifier=2的记录，筛选出两类：出院日期早于基准入院日期的前序记录，或入院日期晚于基准出院日期的后序记录
3. 排序：按用户ID和入院日期降序排列，匹配示例中需要提取的1、2、3行顺序

示例执行结果 #

针对给定数据集，会返回以下3行：

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