如何用标准SQL关联表查询医嘱前最近的科室转入记录

假设表结构 #

基于需求定义两张表的核心字段（若你的实际字段名不同，替换对应名称即可）：

• PATIENT_ORDERS 表：
  • PATIENT_ID：患者唯一标识
  • HOSPITAL_STAY_ID：单次住院唯一标识（区分同一患者多次住院）
  • ORDER_ID：医嘱唯一标识
  • ORDER_DATETIME：医嘱开立时间
  • ORDER_CATEGORY：医嘱类型（用于区分同类型医嘱）
• PATIENT_TRANSFERS 表：
  • PATIENT_ID：患者唯一标识
  • HOSPITAL_STAY_ID：单次住院唯一标识
  • ADT_DATETIME：科室到达时间
  • DEPARTMENT_NAME：科室名称（手术室标记为'OR'）

解决方案SQL #

兼容多数SQL方言的版本（无QUALIFY） #

WITH ORDERED_ORDERS AS (
    -- 给单次住院内的同类型医嘱按时间排序，生成序号区分第二条
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY PATIENT_ID, HOSPITAL_STAY_ID, ORDER_CATEGORY
            ORDER BY ORDER_DATETIME ASC
        ) AS ORDER_SEQ
    FROM PATIENT_ORDERS
),
PATIENT_OR_ADMISSIONS AS (
    -- 找到每条医嘱时间之前的所有手术室转入记录，按时间倒序排序
    SELECT
        pt.PATIENT_ID,
        pt.HOSPITAL_STAY_ID,
        oo.ORDER_ID,
        pt.ADT_DATETIME AS OR_ADMIT_DATETIME,
        ROW_NUMBER() OVER (
            PARTITION BY pt.PATIENT_ID, pt.HOSPITAL_STAY_ID, oo.ORDER_ID
            ORDER BY pt.ADT_DATETIME DESC
        ) AS OR_SEQ
    FROM PATIENT_TRANSFERS pt
    JOIN ORDERED_ORDERS oo 
        ON pt.PATIENT_ID = oo.PATIENT_ID 
        AND pt.HOSPITAL_STAY_ID = oo.HOSPITAL_STAY_ID
        AND pt.ADT_DATETIME < oo.ORDER_DATETIME
        AND pt.DEPARTMENT_NAME = 'OR'
),
LATEST_OR_BEFORE_ORDER AS (
    -- 筛选每条医嘱对应的最近一次手术室转入时间
    SELECT PATIENT_ID, HOSPITAL_STAY_ID, ORDER_ID, OR_ADMIT_DATETIME
    FROM PATIENT_OR_ADMISSIONS
    WHERE OR_SEQ = 1
),
TRANSFER_CANDIDATES AS (
    -- 关联得到所有符合条件的转科记录，并标记序号
    SELECT
        oo.PATIENT_ID,
        oo.HOSPITAL_STAY_ID,
        oo.ORDER_ID,
        oo.ORDER_DATETIME,
        oo.ORDER_SEQ,
        pt.ADT_DATETIME AS LATEST_TRANSFER_BEFORE_ORDER,
        pt.DEPARTMENT_NAME AS LATEST_DEPARTMENT,
        ROW_NUMBER() OVER (
            PARTITION BY oo.ORDER_ID
            ORDER BY pt.ADT_DATETIME DESC
        ) AS TRANSFER_SEQ
    FROM ORDERED_ORDERS oo
    LEFT JOIN LATEST_OR_BEFORE_ORDER lo 
        ON oo.PATIENT_ID = lo.PATIENT_ID 
        AND oo.HOSPITAL_STAY_ID = lo.HOSPITAL_STAY_ID
        AND oo.ORDER_ID = lo.ORDER_ID
    LEFT JOIN PATIENT_TRANSFERS pt 
        ON oo.PATIENT_ID = pt.PATIENT_ID 
        AND oo.HOSPITAL_STAY_ID = pt.HOSPITAL_STAY_ID
        AND pt.ADT_DATETIME < oo.ORDER_DATETIME
        AND pt.ADT_DATETIME >= lo.OR_ADMIT_DATETIME
)
-- 筛选出每条医嘱对应的最近转科记录
SELECT
    PATIENT_ID,
    HOSPITAL_STAY_ID,
    ORDER_ID,
    ORDER_DATETIME,
    ORDER_SEQ,
    LATEST_TRANSFER_BEFORE_ORDER,
    LATEST_DEPARTMENT
FROM TRANSFER_CANDIDATES
WHERE TRANSFER_SEQ = 1
-- 如果仅需展示第二条医嘱的结果，取消注释下面的行
-- AND ORDER_SEQ = 2
ORDER BY PATIENT_ID, HOSPITAL_STAY_ID, ORDER_SEQ;

支持QUALIFY的简化版本（如BigQuery、Snowflake、PostgreSQL 13+） #

WITH ORDERED_ORDERS AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY PATIENT_ID, HOSPITAL_STAY_ID, ORDER_CATEGORY
            ORDER BY ORDER_DATETIME ASC
        ) AS ORDER_SEQ
    FROM PATIENT_ORDERS
),
LATEST_OR_BEFORE_ORDER AS (
    SELECT
        pt.PATIENT_ID,
        pt.HOSPITAL_STAY_ID,
        oo.ORDER_ID,
        pt.ADT_DATETIME AS OR_ADMIT_DATETIME
    FROM PATIENT_TRANSFERS pt
    JOIN ORDERED_ORDERS oo 
        ON pt.PATIENT_ID = oo.PATIENT_ID 
        AND pt.HOSPITAL_STAY_ID = oo.HOSPITAL_STAY_ID
        AND pt.ADT_DATETIME < oo.ORDER_DATETIME
        AND pt.DEPARTMENT_NAME = 'OR'
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY pt.PATIENT_ID, pt.HOSPITAL_STAY_ID, oo.ORDER_ID
        ORDER BY pt.ADT_DATETIME DESC
    ) = 1
)
SELECT
    oo.PATIENT_ID,
    oo.HOSPITAL_STAY_ID,
    oo.ORDER_ID,
    oo.ORDER_DATETIME,
    oo.ORDER_SEQ,
    pt.ADT_DATETIME AS LATEST_TRANSFER_BEFORE_ORDER,
    pt.DEPARTMENT_NAME AS LATEST_DEPARTMENT
FROM ORDERED_ORDERS oo
LEFT JOIN LATEST_OR_BEFORE_ORDER lo 
    ON oo.PATIENT_ID = lo.PATIENT_ID 
    AND oo.HOSPITAL_STAY_ID = lo.HOSPITAL_STAY_ID
    AND oo.ORDER_ID = lo.ORDER_ID
LEFT JOIN PATIENT_TRANSFERS pt 
    ON oo.PATIENT_ID = pt.PATIENT_ID 
    AND oo.HOSPITAL_STAY_ID = pt.HOSPITAL_STAY_ID
    AND pt.ADT_DATETIME < oo.ORDER_DATETIME
    AND pt.ADT_DATETIME >= lo.OR_ADMIT_DATETIME
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY oo.ORDER_ID
    ORDER BY pt.ADT_DATETIME DESC
) = 1
-- AND ORDER_SEQ = 2
ORDER BY PATIENT_ID, HOSPITAL_STAY_ID, ORDER_SEQ;

逻辑说明 #

1. ORDERED_ORDERS：通过ROW_NUMBER()给同一患者单次住院内的同类型医嘱按时间排序，生成ORDER_SEQ，方便精准定位第二条医嘱。
2. LATEST_OR_BEFORE_ORDER：筛选出每条医嘱时间之前的最近一次手术室转入时间，确保后续匹配的转科记录都在这次手术之后。
3. 最终关联：筛选出手术室转入之后、医嘱时间之前的所有转科记录，再通过ROW_NUMBER()取每条医嘱对应的最近转科记录（时间最晚的那条）。

注意事项 #

• 如果患者某次医嘱前没有符合条件的转科记录，结果中LATEST_TRANSFER_BEFORE_ORDER和LATEST_DEPARTMENT会显示为NULL。
• 若仅需展示第二条医嘱的结果，取消SQL中AND ORDER_SEQ = 2的注释即可。

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