基于CASE语句关联多表生成用药标识的SQL问题求助

云上老周

2026-7-8

问题描述

运行环境：SQL Server Management Studio v18.1
现有两个查询：

第一个查询返回153条符合特定手术代码、日期条件的患者就诊记录
第二个查询返回这些患者的关联用药记录（数千行）
需求：合并两个查询，为每条就诊记录新增Med_Flg列——患者使用指定药物则为1，否则为0。
当前尝试用CASE WHEN实现，但查询运行缓慢，且返回不符合手术日期和类型条件的行，推测连接方式或CASE WHEN使用有误。

原就诊记录查询（返回153条）

SELECT TOP(1000) pe.PatientEncounterID
      ,pt.MRN
      ,pe.HospitalAccountID
      ,hacpt.CPT
      ,hacpt.CPTDSC
      ,hacpt.ProcedurePerformDT
      ,pe.EncounterTypeDSC
      ,pe.AppointmentStatusDSC
      ,pe.VisitTypeID
      ,pe.VisitTypeDSC
      ,hacpt.PerformingProviderID
      ,prv.ProviderNM
      ,prv.PrimarySpecialtyDSC
      ,pe.PatientDetailTypeDSC
 FROM EDW.Encounter.PatientEncounter pe
 JOIN EDW.Billing.HospitalAccountCPT hacpt ON hacpt.HospitalAccountID = pe.HospitalAccountID
 JOIN EDW.Provider.Provider prv ON prv.ProviderID = pe.VisitProviderID
 JOIN EDW.Patient.Patient pt ON pt.PatientID = pe.PatientID
 WHERE 
  hacpt.ProcedurePerformDT > '2023-05-01'
  AND pe.AppointmentStatusID = 2
  AND hacpt.CPT IN ('00731', '00732', '00740', '00810', '00811',
'00813', '0397T', '3130F', '43254', '43266', '43273', '44360', '44361',
'44370', '44383', '44384', '44397', '44402', '44403', '45347', '45349',
'45387', '45389', '45390', '47550', '47552', '47553', '47554', '76975',
'9124', 'C9779')
AND prv.ProviderID IN ('30638', '35018', '08707', '08700', '29288', '08711', '32763', '33093', '28411', '28326')

原用药记录查询

SELECT    omed.PatientEncounterID
      ,omed.StartDT
      ,omed.EndDT
      ,omed.DiscontinueDTS
      ,emed.EpicMedicationID
      ,emed.GenericProductID
      ,emed.MedicationNM
      ,emed.PharmaceuticalClassCD
      ,emed.PharmaceuticalClassDSC
      ,emed.PharmaceuticalSubclassCD
      ,emed.PharmaceuticalSubclassDSC
      ,emed.GrouperMedicationID
      ,CASE WHEN emed.ProductTypeID = 2 THEN 'OTC' ELSE 'Prescription' END AS MedType
    FROM Reference.EpicMedication emed
    LEFT JOIN Orders.OrderMedication omed ON omed.EpicMedicationID = emed.EpicMedicationID
    WHERE omed.EndDT > GETDATE()
        AND (omed.DiscontinueDTS IS NULL OR omed.DiscontinueDTS > GETDATE())
        AND (
        (omed.OrderStatusDSC IS NULL) OR omed.OrderStatusDSC NOT IN ('Canceled') )
        AND (GenericNM LIKE '%apixaban%'
        OR GenericNM LIKE '%Aspirin%'
        OR GenericNM LIKE '%Cilostazol%'
        OR GenericNM LIKE '%Clopidogrel%'
        OR GenericNM LIKE '%Dapigatran%'
        OR GenericNM LIKE '%Edoxaban%'
        OR GenericNM LIKE '%Prasugrel%'
        OR GenericNM LIKE '%Ticlopidine%'
        OR GenericNM LIKE '%Rivaroxaban%'
        OR GenericNM LIKE '%Warfarin%')
    GROUP BY omed.PatientEncounterID
      ,omed.StartDT
      ,omed.EndDT
      ,omed.DiscontinueDTS
      ,emed.EpicMedicationID
      ,emed.GenericProductID
      ,emed.MedicationNM
      ,emed.PharmaceuticalClassCD
      ,emed.PharmaceuticalClassDSC
      ,emed.PharmaceuticalSubclassCD
      ,emed.PharmaceuticalSubclassDSC
      ,emed.GrouperMedicationID
      ,CASE WHEN emed.ProductTypeID = 2 THEN 'OTC' ELSE 'Prescription' END

错误的合并尝试

SELECT TOP(1000) pe.PatientEncounterID
      ,pt.MRN
      ,pe.HospitalAccountID
      ,hacpt.CPT
      ,hacpt.CPTDSC
      ,hacpt.ProcedurePerformDT
      ,pe.EncounterTypeDSC
      ,pe.AppointmentStatusDSC
      ,pe.VisitTypeID
      ,pe.VisitTypeDSC
      ,hacpt.PerformingProviderID
      ,prv.ProviderNM
      ,prv.PrimarySpecialtyDSC
      ,pe.PatientDetailTypeDSC
      ,CASE WHEN (emed.GenericNM LIKE '%apixaban%'
        OR emed.GenericNM LIKE '%Aspirin%'
        OR emed.GenericNM LIKE '%Cilostazol%'
        OR emed.GenericNM LIKE '%Clopidogrel%'
        OR emed.GenericNM LIKE '%Dapigatran%'
        OR emed.GenericNM LIKE '%Edoxaban%'
        OR emed.GenericNM LIKE '%Prasugrel%'
        OR emed.GenericNM LIKE '%Ticlopidine%'
        OR emed.GenericNM LIKE '%Rivaroxaban%'
        OR emed.GenericNM LIKE '%Warfarin%'
        ) THEN 1 ELSE 0 END AS "Med_Flg"
 FROM EDW.Encounter.PatientEncounter pe
 JOIN EDW.Billing.HospitalAccountCPT hacpt ON hacpt.HospitalAccountID = pe.HospitalAccountID
 JOIN EDW.Provider.Provider prv ON prv.ProviderID = pe.VisitProviderID
 JOIN EDW.Patient.Patient pt ON pt.PatientID = pe.PatientID
    LEFT JOIN Reference.EpicMedication emed
    LEFT JOIN Orders.OrderMedication omed ON omed.EpicMedicationID = emed.EpicMedicationID ON pe.PatientEncounterID = omed.PatientEncounterID
 WHERE 
  hacpt.ProcedurePerformDT > '2023-05-01'
  AND AppointmentStatusID = 2
  AND CPT IN ('00731', '00732', '00740', '00810', '00811',
'00813', '0397T', '3130F', '43254', '43266', '43273', '44360', '44361',
'44370', '44383', '44384', '44397', '44402', '44403', '45347', '45349',
'45387', '45389', '45390', '47550', '47552', '47553', '47554', '76975',
'9124', 'C9779')
AND prv.ProviderID IN ('30638', '35018', '08707', '08700', '29288', '08711', '32763', '33093', '28411', '28326')
AND (omed.OrderStatusDSC IS NULL ) 
  OR (omed.OrderStatusDSC NOT IN ('Canceled')
  )
  AND omed.EndDT > GETDATE()
GROUP BY pe.PatientEncounterID
      ,pt.MRN
      ,prv.ProviderNM
      ,hacpt.ProcedurePerformDT
      ,hacpt.CPT
      ,pe.HospitalAccountID
      ,hacpt.CPTDSC
      ,pe.EncounterTypeDSC
      ,pe.AppointmentStatusDSC
      ,pe.VisitTypeID
      ,pe.VisitTypeDSC
      ,hacpt.PerformingProviderID
      ,prv.PrimarySpecialtyDSC
      ,pe.PatientDetailTypeDSC
      ,CASE WHEN (GenericNM LIKE '%apixaban%'
        OR GenericNM LIKE '%Aspirin%'
        OR GenericNM LIKE '%Cilostazol%'
        OR GenericNM LIKE '%Clopidogrel%'
        OR GenericNM LIKE '%Dapigatran%'
        OR GenericNM LIKE '%Edoxaban%'
        OR GenericNM LIKE '%Prasugrel%'
        OR GenericNM LIKE '%Ticlopidine%'
        OR GenericNM LIKE '%Rivaroxaban%'
        OR GenericNM LIKE '%Warfarin%'
        ) THEN 1 ELSE 0 END

问题根源

WHERE子句逻辑错误：OR运算符优先级低于AND，导致原就诊筛选条件被拆分，部分不符合手术日期/类型的行因满足用药条件被返回。
连接顺序错误：LEFT JOIN嵌套顺序混乱，导致关联逻辑出错，可能产生冗余行。
冗余计算与分组：直接在主查询中判断药物类型并分组，增加了查询复杂度和运行时间。

正确的合并查询

方法1：预聚合用药标记（性能优先）

先将用药记录按就诊ID聚合，生成是否使用目标药物的标记，再关联到就诊查询，避免大量行的冗余关联：

WITH MedFlags AS (
    SELECT 
        omed.PatientEncounterID,
        MAX(CASE 
            WHEN emed.GenericNM LIKE '%apixaban%'
                OR emed.GenericNM LIKE '%Aspirin%'
                OR emed.GenericNM LIKE '%Cilostazol%'
                OR emed.GenericNM LIKE '%Clopidogrel%'
                OR emed.GenericNM LIKE '%Dapigatran%'
                OR emed.GenericNM LIKE '%Edoxaban%'
                OR emed.GenericNM LIKE '%Prasugrel%'
                OR emed.GenericNM LIKE '%Ticlopidine%'
                OR emed.GenericNM LIKE '%Rivaroxaban%'
                OR emed.GenericNM LIKE '%Warfarin%'
            THEN 1 ELSE 0 
        END) AS Med_Flg
    FROM Reference.EpicMedication emed
    JOIN Orders.OrderMedication omed ON omed.EpicMedicationID = emed.EpicMedicationID
    WHERE 
        omed.EndDT > GETDATE()
        AND (omed.DiscontinueDTS IS NULL OR omed.DiscontinueDTS > GETDATE())
        AND (omed.OrderStatusDSC IS NULL OR omed.OrderStatusDSC NOT IN ('Canceled'))
    GROUP BY omed.PatientEncounterID
)
SELECT 
    pe.PatientEncounterID,
    pt.MRN,
    pe.HospitalAccountID,
    hacpt.CPT,
    hacpt.CPTDSC,
    hacpt.ProcedurePerformDT,
    pe.EncounterTypeDSC,
    pe.AppointmentStatusDSC,
    pe.VisitTypeID,
    pe.VisitTypeDSC,
    hacpt.PerformingProviderID,
    prv.ProviderNM,
    prv.PrimarySpecialtyDSC,
    pe.PatientDetailTypeDSC,
    ISNULL(mf.Med_Flg, 0) AS Med_Flg
FROM EDW.Encounter.PatientEncounter pe
JOIN EDW.Billing.HospitalAccountCPT hacpt ON hacpt.HospitalAccountID = pe.HospitalAccountID
JOIN EDW.Provider.Provider prv ON prv.ProviderID = pe.VisitProviderID
JOIN EDW.Patient.Patient pt ON pt.PatientID = pe.PatientID
LEFT JOIN MedFlags mf ON mf.PatientEncounterID = pe.PatientEncounterID
WHERE 
    hacpt.ProcedurePerformDT > '2023-05-01'
    AND pe.AppointmentStatusID = 2
    AND hacpt.CPT IN ('00731', '00732', '00740', '00810', '00811',
'00813', '0397T', '3130F', '43254', '43266', '43273', '44360', '44361',
'44370', '44383', '44384', '44397', '44402', '44403', '45347', '45349',
'45387', '45389', '45390', '47550', '47552', '47553', '47554', '76975',
'9124', 'C9779')
AND prv.ProviderID IN ('30638', '35018', '08707', '08700', '29288', '08711', '32763', '33093', '28411', '28326')

方法2：使用EXISTS子句（逻辑简洁）

直接在SELECT中用EXISTS判断当前就诊是否有符合条件的用药记录，无需预聚合：

SELECT 
    pe.PatientEncounterID,
    pt.MRN,
    pe.HospitalAccountID,
    hacpt.CPT,
    hacpt.CPTDSC,
    hacpt.ProcedurePerformDT,
    pe.EncounterTypeDSC,
    pe.AppointmentStatusDSC,
    pe.VisitTypeID,
    pe.VisitTypeDSC,
    hacpt.PerformingProviderID,
    prv.ProviderNM,
    prv.PrimarySpecialtyDSC,
    pe.PatientDetailTypeDSC,
    CASE 
        WHEN EXISTS (
            SELECT 1
            FROM Reference.EpicMedication emed
            JOIN Orders.OrderMedication omed ON omed.EpicMedicationID = emed.EpicMedicationID
            WHERE 
                omed.PatientEncounterID = pe.PatientEncounterID
                AND omed.EndDT > GETDATE()
                AND (omed.DiscontinueDTS IS NULL OR omed.DiscontinueDTS > GETDATE())
                AND (omed.OrderStatusDSC IS NULL OR omed.OrderStatusDSC NOT IN ('Canceled'))
                AND (emed.GenericNM LIKE '%apixaban%'
                    OR emed.GenericNM LIKE '%Aspirin%'
                    OR emed.GenericNM LIKE '%Cilostazol%'
                    OR emed.GenericNM LIKE '%Clopidogrel%'
                    OR emed.GenericNM LIKE '%Dapigatran%'
                    OR emed.GenericNM LIKE '%Edoxaban%'
                    OR emed.GenericNM LIKE '%Prasugrel%'
                    OR emed.GenericNM LIKE '%Ticlopidine%'
                    OR emed.GenericNM LIKE '%Rivaroxaban%'
                    OR emed.GenericNM LIKE '%Warfarin%')
        ) THEN 1 ELSE 0 
    END AS Med_Flg
FROM EDW.Encounter.PatientEncounter pe
JOIN EDW.Billing.HospitalAccountCPT hacpt ON hacpt.HospitalAccountID = pe.HospitalAccountID
JOIN EDW.Provider.Provider prv ON prv.ProviderID = pe.VisitProviderID
JOIN EDW.Patient.Patient pt ON pt.PatientID = pe.PatientID
WHERE 
    hacpt.ProcedurePerformDT > '2023-05-01'
    AND pe.AppointmentStatusID = 2
    AND hacpt.CPT IN ('00731', '00732', '00740', '00810', '00811',
'00813', '0397T', '3130F', '43254', '43266', '43273', '44360', '44361',
'44370', '44383', '44384', '44397', '44402', '44403', '45347', '45349',
'45387', '45389', '45390', '47550', '47552', '47553', '47554', '76975',
'9124', 'C9779

基于CASE语句关联多表生成用药标识的SQL问题求助

原就诊记录查询（返回153条） #

原用药记录查询 #

错误的合并尝试 #

问题根源 #

正确的合并查询 #

方法1：预聚合用药标记（性能优先） #

方法2：使用EXISTS子句（逻辑简洁） #

原就诊记录查询（返回153条）

原用药记录查询

错误的合并尝试

问题根源

正确的合并查询

方法1：预聚合用药标记（性能优先）

方法2：使用EXISTS子句（逻辑简洁）