首呼解决场景下，如何在SQL Server中创建7天联系判断Y/N列？

针对你的需求，以下是几种在SQL Server中实现的实用方法：

方法一：查询时动态生成结果列（无需修改表结构） #

如果只是需要在查询结果中返回这两个判断列，用EXISTS子查询即可快速实现：

SELECT 
    customerId,
    DateOfContact,
    ContactReason,
    -- 判断过去7天是否有其他联系
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM CustomerContacts c2 
            WHERE c2.customerId = c1.customerId
              -- 范围：当前记录日期的前7天到前1天，排除当天
              AND c2.DateOfContact BETWEEN DATEADD(DAY, -7, c1.DateOfContact) 
                                       AND DATEADD(DAY, -1, c1.DateOfContact)
              -- 排除当前记录本身（需替换为表的主键列，比如ContactId）
              AND c2.ContactId != c1.ContactId
        ) THEN 'Y' 
        ELSE 'N' 
    END AS 7DayContact,
    -- 判断过去7天是否有相同原因的联系
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM CustomerContacts c2 
            WHERE c2.customerId = c1.customerId
              AND c2.ContactReason = c1.ContactReason
              AND c2.DateOfContact BETWEEN DATEADD(DAY, -7, c1.DateOfContact) 
                                       AND DATEADD(DAY, -1, c1.DateOfContact)
              AND c2.ContactId != c1.ContactId
        ) THEN 'Y' 
        ELSE 'N' 
    END AS 7DaySameContact
FROM CustomerContacts c1;

关键说明 #

• 如果需要包含当天的联系记录，可将日期条件改为c2.DateOfContact <= c1.DateOfContact AND c2.DateOfContact >= DATEADD(DAY, -7, c1.DateOfContact)，但必须保留排除当前记录的条件，避免误判。

方法二：新增持久化计算列（修改表结构） #

如果需要把这两个列永久保存在表中，可以添加计算列，数据更新时列值会自动同步：

-- 添加7DayContact列
ALTER TABLE CustomerContacts
ADD 7DayContact AS 
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM CustomerContacts c2 
            WHERE c2.customerId = CustomerContacts.customerId
              AND c2.DateOfContact BETWEEN DATEADD(DAY, -7, CustomerContacts.DateOfContact) 
                                       AND DATEADD(DAY, -1, CustomerContacts.DateOfContact)
              AND c2.ContactId != CustomerContacts.ContactId
        ) THEN 'Y' 
        ELSE 'N' 
    END PERSISTED;

-- 添加7DaySameContact列
ALTER TABLE CustomerContacts
ADD 7DaySameContact AS 
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM CustomerContacts c2 
            WHERE c2.customerId = CustomerContacts.customerId
              AND c2.ContactReason = CustomerContacts.ContactReason
              AND c2.DateOfContact BETWEEN DATEADD(DAY, -7, CustomerContacts.DateOfContact) 
                                       AND DATEADD(DAY, -1, CustomerContacts.DateOfContact)
              AND c2.ContactId != CustomerContacts.ContactId
        ) THEN 'Y' 
        ELSE 'N' 
    END PERSISTED;

注意事项 #

• PERSISTED关键字会把计算结果存储在表中，提升查询性能但会占用存储空间；若不需要持久化，可去掉该关键字，每次查询时动态计算。
• 如果表没有主键列，需用其他唯一标识（比如组合DateOfContact+ContactReason）排除当前记录，但这种方式存在重复数据误判风险，建议优先给表添加主键。

方法三：窗口函数实现（大数据量更高效） #

如果表数据量较大，用窗口函数的批量统计性能更优：

SELECT 
    customerId,
    DateOfContact,
    ContactReason,
    CASE 
        WHEN COUNT(*) OVER (
            PARTITION BY customerId 
            ORDER BY DateOfContact 
            RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND INTERVAL 1 DAY PRECEDING
        ) > 0 THEN 'Y' 
        ELSE 'N' 
    END AS 7DayContact,
    CASE 
        WHEN COUNT(*) OVER (
            PARTITION BY customerId, ContactReason 
            ORDER BY DateOfContact 
            RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND INTERVAL 1 DAY PRECEDING
        ) > 0 THEN 'Y' 
        ELSE 'N' 
    END AS 7DaySameContact
FROM CustomerContacts;

关键说明 #

• PARTITION BY按指定字段分组，RANGE BETWEEN定义日期范围，统计范围内的记录数即可判断是否存在符合条件的联系。

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