SQL Server中基于规则新增列：识别出院后首个就诊地点

嘿，我来帮你搞定这个SQL Server里的医疗记录分析需求！要识别患者出院后的首个就诊地点，咱们可以用CTE（公共表表达式）结合窗口函数来实现，思路清晰还容易维护。

核心思路 #

咱们分两步走：

• 先给每个患者（按ID分组）确定出院日期：也就是每组里location='initial'行的end_date（你提到这是每组首行，不过保险起见咱们还是用条件筛选，避免排序异常问题）；
• 再在每个患者的后续记录里，找出出院后最早的就诊记录，提取它的location作为首个就诊地点。

完整SQL代码 #

WITH PatientDischarge AS (
    -- 第一步：获取每个患者的出院日期
    SELECT 
        ID,
        end_date AS discharge_date
    FROM 
        YourMedicalRecordsTable
    WHERE 
        location = 'initial' -- 定位出院记录行
),
PostDischargeVisits AS (
    -- 第二步：标记每个患者出院后的就诊记录，并按时间排序
    SELECT 
        r.ID,
        r.location,
        r.begin_date,
        -- 给每个ID的出院后记录按就诊时间排号，最早的是1
        ROW_NUMBER() OVER (PARTITION BY r.ID ORDER BY r.begin_date ASC) AS visit_rank
    FROM 
        YourMedicalRecordsTable r
    JOIN 
        PatientDischarge d ON r.ID = d.ID
    WHERE 
        r.begin_date > d.discharge_date -- 只筛选出院后的记录
        AND r.location != 'initial' -- 排除出院本身的记录
)
-- 第三步：将首个就诊地点关联回原表，新增列展示
SELECT 
    r.*,
    COALESCE(pdv.location, '无后续就诊') AS first_post_discharge_location
FROM 
    YourMedicalRecordsTable r
LEFT JOIN 
    PostDischargeVisits pdv ON r.ID = pdv.ID AND pdv.visit_rank = 1;

代码细节说明 #

• PatientDischarge CTE：专门提取每个患者的出院日期，确保我们拿到的是正确的出院时间点；
• PostDischargeVisits CTE：用ROW_NUMBER()窗口函数给每个患者的出院后就诊记录按时间排序，排名为1的就是首个就诊；
• COALESCE函数：处理没有后续就诊的情况，返回友好的提示文本（你也可以改成NULL，根据需求调整）；
• 如果你需要永久新增列，更推荐创建视图来实时展示结果（因为计算列无法直接引用其他行的数据）：

-- 创建视图，实时展示首个就诊地点
CREATE VIEW MedicalRecordsWithFirstVisit AS
WITH PatientDischarge AS (
    SELECT 
        ID,
        end_date AS discharge_date
    FROM 
        YourMedicalRecordsTable
    WHERE 
        location = 'initial'
),
PostDischargeVisits AS (
    SELECT 
        r.ID,
        r.location,
        ROW_NUMBER() OVER (PARTITION BY r.ID ORDER BY r.begin_date ASC) AS visit_rank
    FROM 
        YourMedicalRecordsTable r
    JOIN 
        PatientDischarge d ON r.ID = d.ID
    WHERE 
        r.begin_date > d.discharge_date
        AND r.location != 'initial'
)
SELECT 
    r.*,
    COALESCE(pdv.location, '无后续就诊') AS first_post_discharge_location
FROM 
    YourMedicalRecordsTable r
LEFT JOIN 
    PostDischargeVisits pdv ON r.ID = pdv.ID AND pdv.visit_rank = 1;

这样你查询视图的时候就能直接看到每个患者的首个出院后就诊地点啦！

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