如何修改T-SQL查询，输出基于两个时段出现两次及以上的记录？

根据你的需求，我们要筛选出ArrivalDate在2018-04-01至2018-12-31之间，且对应的ResID同时存在于其他ArrivalDate时段、总出现次数≥2的记录。结合原查询取每个ResID第一条记录的逻辑，这里提供两种清晰的实现思路：

思路1：先锁定符合条件的ResID，再筛选目标时段记录 #

这种方法先通过子查询找出满足跨时段且多次出现的ResID，再关联回原数据集筛选目标时段的记录，同时保留原查询的取数逻辑：

USE MyDatabase;

WITH ResID_Filter AS (
    -- 找出同时存在目标时段和其他时段、总记录数≥2的ResID
    SELECT ResID
    FROM View1
    GROUP BY ResID
    HAVING 
        -- 存在目标时段的记录
        SUM(CASE WHEN ArrivalDate BETWEEN '2018-04-01' AND '2018-12-31' THEN 1 ELSE 0 END) > 0
        -- 存在其他时段的记录
        AND SUM(CASE WHEN ArrivalDate NOT BETWEEN '2018-04-01' AND '2018-12-31' THEN 1 ELSE 0 END) > 0
        -- 总出现次数≥2（满足前两个条件时该条自动成立，可按需省略）
        AND COUNT(*) >= 2
),
Query_CTE AS (
    SELECT 
        ResID, 
        Name, 
        ArrivalDate, 
        Status, 
        ProfileID,
        -- 按StayDate排序取每个ResID的第一条记录
        ROW_NUMBER() OVER(PARTITION BY ResID ORDER BY StayDate) AS xy
    FROM View1
    -- 仅保留符合条件的ResID，且ArrivalDate在目标区间内
    WHERE 
        ResID IN (SELECT ResID FROM ResID_Filter)
        AND ArrivalDate BETWEEN '2018-04-01' AND '2018-12-31'
)
SELECT ResID, Name, ArrivalDate, Status, ProfileID
FROM Query_CTE 
WHERE xy = 1;

思路2：在CTE中直接整合筛选条件 #

如果不需要单独提取ResID，也可以把跨时段判断逻辑整合到主CTE中，通过窗口函数一次性完成标记：

USE MyDatabase;

WITH Query_CTE AS (
    SELECT 
        ResID, 
        Name, 
        ArrivalDate, 
        Status, 
        ProfileID,
        ROW_NUMBER() OVER(PARTITION BY ResID ORDER BY StayDate) AS xy,
        -- 标记该ResID是否有目标时段外的记录
        MAX(CASE WHEN ArrivalDate NOT BETWEEN '2018-04-01' AND '2018-12-31' THEN 1 ELSE 0 END) OVER(PARTITION BY ResID) AS Has_Other_Period,
        -- 统计该ResID的总记录数
        COUNT(*) OVER(PARTITION BY ResID) AS Total_Records
    FROM View1
    WHERE ArrivalDate BETWEEN '2018-04-01' AND '2018-12-31'
)
SELECT ResID, Name, ArrivalDate, Status, ProfileID
FROM Query_CTE 
WHERE 
    xy = 1
    AND Has_Other_Period = 1
    AND Total_Records >= 2;

关键说明 #

• 两种思路都保留了原查询中ROW_NUMBER() OVER(PARTITION BY ResID ORDER BY StayDate)取每个ResID第一条记录的逻辑；
• 若你的“出现两次及以上”特指目标时段内出现两次及以上，同时其他时段也有记录，可调整HAVING或窗口函数中的条件，把目标时段内的计数判断改为≥2；
• 建议给View1中的ResID和ArrivalDate字段建立索引，提升大数据量下的查询效率。

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