MS SQL Server房屋与住户服务周期数据查询问题求助

问题背景 #

需从MS SQL Server数据库提取房屋（Premise）、住户账户（Account）及其对应服务周期数据，覆盖三类场景：

• 住户迁入后持续居住至今
• 住户迁入后居住一段时间已迁出
• 住户迁入后迁出又回迁（公寓场景，此场景为查询难点）

数据库中accountservice表记录房屋的激活服务（如水、污水、垃圾处理等），包含fromdate（服务开始/入住时间）和todate（服务结束/迁出时间）字段，仅需关注近三年有服务记录的账户。当前查询在处理回迁场景时输出不符合预期，理想输出需按时间顺序展示每个住户的完整服务周期，且不能出现同一房屋同一时间段存在多个住户的错误。曾尝试用MIN(fromdate)、MAX(todate)聚合，但无法覆盖回迁场景的非连续周期。

示例数据 #

Premise#  - Account# -   From Date  -   To Date  -- 说明
1. 00125466,   146732,    2/2/2001,     NULL      -- 持续居住至今
2. 10085279,   180887,    6/12/1978,  1/15/2020  -- 迁入后已迁出
3. 00005110,  **267110**, 10/15/2020, 12/14/2021 -- 2020.10-2021.12居住
3a.00005110,   273341,    12/14/2021, 12/09/2022 -- 2021.12-2022.12居住
3b.00005110,  **267110**, 12/09/2022, NULL      -- 2022.12回迁后居住至今

当前问题SQL #

SELECT DISTINCT TOP (100) PERCENT 
    dbo.servicelocation.propertynumber, dbo.account.accountnumber, 
    dbo.accountservice.fromdate, dbo.accountservice.todate
FROM
    dbo.account 
INNER JOIN
    dbo.accountservice ON dbo.account.account_id = dbo.accountservice.account_id 
INNER JOIN
    dbo.service ON dbo.accountservice.service_id = dbo.service.service_id 
INNER JOIN
    dbo.servicelocation ON dbo.service.servicelocation_id = dbo.servicelocation.servicelocation_id
WHERE  
    (dbo.servicelocation.propertynumber = '00005110') 
    AND (dbo.accountservice.todate > CONVERT(DATETIME, '2021-01-01 00:00:00', 102)) OR
        (dbo.servicelocation.propertynumber = '00005110') 
    AND (dbo.accountservice.todate IS NULL)
ORDER BY 
    dbo.accountservice.fromdate DESC

问题分析 #

1. 冗余语法：DISTINCT TOP (100) PERCENT无实际意义，ORDER BY后TOP 100 PERCENT等价于返回所有行，且DISTINCT无法彻底解决同一账户同一房屋的重复服务记录问题。
2. 筛选逻辑不完善：未考虑todate为NULL时fromdate需在近三年范围内的情况，可能会返回过旧的历史记录。
3. 回迁场景处理缺失：未区分同一账户的非连续居住周期，导致回迁后的周期无法正确独立展示。

解决方案SQL #

WITH FilteredServiceCycles AS (
    -- 筛选近三年服务记录并去重，聚合同一账户同一房屋的同周期服务
    SELECT 
        sl.propertynumber AS [Premise#],
        a.accountnumber AS [Account#],
        MIN(asrv.fromdate) AS [From Date],
        MAX(asrv.todate) AS [To Date]
    FROM dbo.account a
    INNER JOIN dbo.accountservice asrv 
        ON a.account_id = asrv.account_id
    INNER JOIN dbo.service s 
        ON asrv.service_id = s.service_id
    INNER JOIN dbo.servicelocation sl 
        ON s.servicelocation_id = sl.servicelocation_id
    WHERE 
        sl.propertynumber = '00005110'
        AND (
            -- 已迁出且结束时间在近三年
            asrv.todate >= DATEADD(YEAR, -3, GETDATE())
            -- 未迁出且开始时间在近三年
            OR (asrv.todate IS NULL AND asrv.fromdate >= DATEADD(YEAR, -3, GETDATE()))
        )
    GROUP BY sl.propertynumber, a.accountnumber, asrv.fromdate, asrv.todate
),
OrderedCycles AS (
    -- 为每个账户的居住周期编号，区分回迁的不同阶段
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY [Premise#], [Account#] ORDER BY [From Date]) AS CycleSeq
    FROM FilteredServiceCycles
)
-- 按时间顺序输出，将NULL的结束时间显示为"至今"
SELECT 
    [Premise#],
    [Account#],
    [From Date],
    CASE 
        WHEN [To Date] IS NULL THEN '至今' 
        ELSE CONVERT(VARCHAR(10), [To Date], 101) 
    END AS [To Date]
FROM OrderedCycles
ORDER BY [Premise#], [From Date];

方案说明 #

1. FilteredServiceCycles CTE：通过GROUP BY聚合同一账户同一房屋的同周期服务记录，避免因多服务类型产生重复行；同时完善筛选逻辑，确保仅保留近三年的有效记录。
2. OrderedCycles CTE：使用窗口函数ROW_NUMBER()为每个账户的非连续居住周期编号，清晰区分回迁前后的不同阶段。
3. 最终输出：将todate为NULL的情况转换为“至今”，按房屋和入住时间升序排列，确保时间顺序正确，无重叠数据。

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