SQL Server 2014下如何按特定日期条件过滤t1表数据

针对你描述的需求，我们可以通过窗口函数来实现精准的记录筛选，既满足保留符合条件的客人记录，又能排除不符合规则的条目。以下是具体的解决方案：

实现思路 #

1. 对每个客人（按GuestID分组），先获取其第一条记录（rownum=1）的离店日期DepartureDate，记为FirstDeparture。
2. 判断每个客人是否存在后续记录（rownum≥2）满足到店日期ArrivalDate晚于FirstDeparture，标记为HasValidSubsequent。
3. 最后筛选出两类记录：
   • 客人的第一条记录，且该客人存在符合条件的后续记录；
   • 客人的后续记录，且自身到店日期晚于FirstDeparture。

完整SQL查询 #

WITH GuestBase AS (
    SELECT 
        rownum,
        RoomID,
        ArrivalDate,
        DepartureDate,
        Name,
        GuestID,
        -- 获取当前客人第一条记录的离店日期
        MAX(CASE WHEN rownum = 1 THEN DepartureDate END) OVER (PARTITION BY GuestID) AS FirstDeparture,
        -- 判断当前客人是否有符合条件的后续记录
        MAX(CASE 
                WHEN rownum >= 2 AND ArrivalDate > MAX(CASE WHEN rownum = 1 THEN DepartureDate END) OVER (PARTITION BY GuestID) 
                THEN 1 
                ELSE 0 
            END) OVER (PARTITION BY GuestID) AS HasValidSubsequent
    FROM t1
)
SELECT 
    rownum, 
    RoomID, 
    ArrivalDate, 
    DepartureDate, 
    Name, 
    GuestID
FROM GuestBase
WHERE 
    (rownum = 1 AND HasValidSubsequent = 1)
    OR (rownum >= 2 AND ArrivalDate > FirstDeparture);

逻辑验证 #

我们结合你的示例数据来验证结果：

• Joe：第二条记录的ArrivalDate（2020-06-13）晚于第一条的DepartureDate（2020-05-10），因此两条记录都被保留。
• Smith：第二条记录的ArrivalDate等于第一条的DepartureDate，被排除；第三条记录的ArrivalDate（2020-09-01）晚于第一条的DepartureDate（2020-07-08），因此保留第一条和第三条记录。
• John/Alan：所有后续记录的ArrivalDate都等于第一条的DepartureDate，因此HasValidSubsequent为0，第一条记录被排除，后续记录也不满足筛选条件，最终全部被排除。

这个方案完全兼容SQL Server 2014，并且逻辑清晰，易于维护。

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