自连接查询仅存单条居民记录时无返回结果的解决方法

我明白你的问题了——单条记录的居民（比如居民200）因为没有上一条出院记录，被现有查询过滤掉了对吧？这是因为你的关联逻辑没考虑到「无历史出院记录」的场景，咱们来调整一下，让这类记录也能被正常返回。

方案一：用LAG窗口函数简化逻辑 #

这种方式比自连接更简洁高效，直接通过窗口函数获取同居民的上一条出院日期：

WITH sorted_records AS (
    SELECT 
        居民ID,
        入院日期,
        出院日期,
        -- 按居民分组、入院日期排序，取上一条记录的出院日期
        LAG(出院日期) OVER (PARTITION BY 居民ID ORDER BY 入院日期) AS 上一次出院日期
    FROM 入院出院状态表
)
SELECT 
    居民ID,
    入院日期,
    出院日期
FROM sorted_records
WHERE 
    -- 两种满足条件的情况：
    -- 1. 没有上一次出院记录（单条入院记录）
    上一次出院日期 IS NULL
    -- 2. 上一次出院到本次入院间隔至少30天
    OR DATEDIFF(DAY, 上一次出院日期, 入院日期) >= 30;

方案二：调整原ROW_NUMBER自连接逻辑 #

如果你想沿用原来的ROW_NUMBER思路，只需要把内连接改成左连接，同时把「无匹配上一条记录」的情况加入筛选条件：

WITH sorted AS (
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY 居民ID ORDER BY 入院日期) AS rn,
        居民ID,
        入院日期,
        出院日期
    FROM 入院出院状态表
)
SELECT 
    curr.居民ID,
    curr.入院日期,
    curr.出院日期
FROM sorted curr
-- 左连接，允许没有匹配的上一条记录
LEFT JOIN sorted prev 
    ON curr.居民ID = prev.居民ID 
    AND curr.rn = prev.rn + 1
WHERE 
    -- 单条记录：没有上一条匹配项
    prev.rn IS NULL 
    -- 或者满足间隔≥30天的条件
    OR DATEDIFF(DAY, prev.出院日期, curr.入院日期) >= 30;

关键逻辑说明 #

核心是要明确：单条入院记录的居民没有历史出院记录，自然符合「上次出院后至少30天入院」的业务要求，所以必须把这种场景加入WHERE条件。不管用窗口函数还是自连接，只要确保这种情况不被过滤，就能得到正确结果。

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