SQL Server关联工单表获取未出租公寓最新周转工单完成日期

核心问题分析 #

你之前的关联方式未针对单元维度过滤工单，仅通过物业关联会导致大量无效匹配（同一物业下所有工单与所有单元关联），引发笛卡尔积，最终导致查询无法完成。需要先预处理工单数据，按「物业+单元」分组获取最新的已完成周转工单，再与原查询关联。

优化后的完整查询语句 #

WITH LatestTenants AS (
    -- 获取每个单元的最后一位租户（按退房日期倒序取最新）
    SELECT 
        t.sunitcode,
        t.sfirstname,
        t.slastname,
        t.istatus,
        t.dtmoveout,
        t.dtmovein,
        ROW_NUMBER() OVER (PARTITION BY t.sunitcode ORDER BY t.dtmoveout DESC) AS rn
    FROM tenant t
    WHERE t.istatus != 0
),
LatestTurnoverWorkOrders AS (
    -- 获取每个单元的最新已完成"Unit Turnover"工单
    SELECT 
        wo.hproperty,
        wo.sunitcode,
        wo.dtwcompl AS LatestTurnoverCompleteDate,
        ROW_NUMBER() OVER (PARTITION BY wo.hproperty, wo.sunitcode ORDER BY wo.dtwcompl DESC) AS rn
    FROM mm2wo wo
    WHERE 
        wo.scategory = 'Unit Turnover'
        AND wo.dtwcompl IS NOT NULL -- 仅筛选已完成的工单
        -- 可选：添加日期范围过滤，比如 AND wo.dtwcompl >= '2023-04-01'
)
SELECT
    p.scode AS '物业编码',
    p.saddr1 AS '物业地址',
    u.scode AS '单元编号',
    u.sstatus AS '单元状态',
    CONVERT(VARCHAR, u.dtvacant, 101) AS '空置日期',
    -- 优先用工单完成日期作为可入住日期，无工单则使用原字段
    CONVERT(VARCHAR, ISNULL(lto.LatestTurnoverCompleteDate, u.dtavailable), 101) AS '可入住日期',
    CONVERT(VARCHAR, u.dtready, 101) AS '准备就绪日期',
    DATEDIFF(DAY, u.dtvacant, GETDATE()) AS '空置天数',
    u.srent AS '租金',
    CASE 
        WHEN u.irentready = 1 THEN '是'
        WHEN u.irentready = 0 THEN '否'
    END AS '是否可出租',
    lt.sfirstname AS '租户名',
    lt.slastname AS '租户姓',
    CASE 
        WHEN lt.istatus = 1 THEN '已退房租户'
        WHEN lt.istatus = 4 THEN '仍在单元/已提退租通知'
    END AS '租户状态',
    CONVERT(VARCHAR, lt.dtmoveout, 101) AS '退房日期',
    CONVERT(VARCHAR, lt.dtmovein, 101) AS '入住日期'
FROM property p
LEFT JOIN unit u 
    ON p.hMy = u.hproperty
LEFT JOIN LatestTenants lt 
    ON u.scode = lt.sunitcode
    AND lt.rn = 1 -- 取每个单元的最后一位租户
LEFT JOIN LatestTurnoverWorkOrders lto 
    ON p.hmy = lto.hproperty
    AND u.scode = lto.sunitcode
    AND lto.rn = 1 -- 取每个单元的最新周转工单
WHERE 
    u.sstatus LIKE '%Unrented%'
    AND p.binactive = 0
ORDER BY p.scode, u.scode

关键优化点 #

1. 租户数据预处理：用ROW_NUMBER()按单元分组，取退房日期最晚的租户，替代原嵌套EXISTS查询，提升性能并简化逻辑。
2. 工单数据预处理：同样用ROW_NUMBER()按「物业+单元」分组，仅保留最新的已完成周转工单，避免无效关联。
3. 可入住日期逻辑：通过ISNULL()优先展示工单完成日期，无对应工单时 fallback 到原dtavailable字段，符合需求。
4. 类型转换优化：直接对数值型字段irentready、istatus做判断，无需转字符串，减少不必要的计算。

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