如何根据paneltest(133)结果筛选关联患者的指定测试数据

需求规则 #

• 若患者的paneltest(133)结果为Reactive，则上报该患者的paneltest(133)、paneltest(2154)、paneltest(2157)所有结果（无论结果是否为Reactive）
• 若患者的paneltest(133)结果为NonReactive，则上述三项结果均不上报

测试数据 #

当前尝试的SQL语句 #

select distinct
pt.panelTestKey,
p.orgChartNumber,
t.name 'test',
r.result

FROM mytables rq

WHERE pt.paneltestKey IN (133)
AND pt.isnonreportable = 0
AND ((CONVERT(varchar(50), dbo.EpochToLocal(rq.finalDeliverystamp), 112)) >= dateadd(day,datediff(day,1,GETDATE()),0))    --shows testing done yesterday;can change day number to pull other date data
AND ((CONVERT(varchar(50), dbo.EpochToLocal(rq.finalDeliverystamp), 112)) < dateadd(day,datediff(day,0,GETDATE()),0))   --shows testing not done today
and r.result='Reactive'
AND valuetoReport = 1

问题说明 #

已尝试CTE、子查询、CASE语句，但均未成功，不清楚实现逻辑。期望结果：patient1的三项测试结果全部显示，patient2因paneltest(133)为NonReactive不显示。

解决方案 #

核心思路是先筛选出paneltest(133)结果为Reactive的患者，再关联获取这些患者的三项目标测试结果，以下提供两种可行方案：

方案1：子查询筛选目标患者 #

SELECT 
    pt.panelTestKey,
    p.orgChartNumber,
    t.name AS 'test',
    r.result
FROM mytables rq
-- 补充原查询缺失的表关联条件（根据实际表结构填写）
JOIN pt ON rq.关联字段 = pt.关联字段
JOIN p ON rq.关联字段 = p.关联字段
JOIN t ON rq.关联字段 = t.关联字段
JOIN r ON rq.关联字段 = r.关联字段
WHERE 
    -- 限定只查询三项目标测试
    pt.panelTestKey IN (133, 2154, 2157)
    AND pt.isnonreportable = 0
    AND valuetoReport = 1
    -- 保留原日期筛选逻辑
    AND CONVERT(varchar(50), dbo.EpochToLocal(rq.finalDeliverystamp), 112) >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0)
    AND CONVERT(varchar(50), dbo.EpochToLocal(rq.finalDeliverystamp), 112) < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
    -- 筛选出符合条件的患者
    AND p.orgChartNumber IN (
        SELECT DISTINCT p_inner.orgChartNumber
        FROM mytables rq_inner
        JOIN pt_inner ON rq_inner.关联字段 = pt_inner.关联字段
        JOIN p_inner ON rq_inner.关联字段 = p_inner.关联字段
        JOIN r_inner ON rq_inner.关联字段 = r_inner.关联字段
        WHERE 
            pt_inner.panelTestKey = 133
            AND pt_inner.isnonreportable = 0
            AND valuetoReport = 1
            AND r_inner.result = 'Reactive'
            -- 同步日期筛选条件
            AND CONVERT(varchar(50), dbo.EpochToLocal(rq_inner.finalDeliverystamp), 112) >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0)
            AND CONVERT(varchar(50), dbo.EpochToLocal(rq_inner.finalDeliverystamp), 112) < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
    )

方案2：窗口函数标记符合条件的患者 #

如果使用的SQL支持窗口函数（如SQL Server 2008+），可以用更简洁的方式实现：

WITH PatientStatus AS (
    SELECT 
        p.orgChartNumber,
        -- 标记患者是否符合上报条件
        MAX(CASE WHEN pt.panelTestKey = 133 AND r.result = 'Reactive' THEN 1 ELSE 0 END) OVER (PARTITION BY p.orgChartNumber) AS IsEligible
    FROM mytables rq
    JOIN pt ON rq.关联字段 = pt.关联字段
    JOIN p ON rq.关联字段 = p.关联字段
    JOIN r ON rq.关联字段 = r.关联字段
    WHERE 
        pt.panelTestKey = 133
        AND pt.isnonreportable = 0
        AND valuetoReport = 1
        -- 同步日期筛选条件
        AND CONVERT(varchar(50), dbo.EpochToLocal(rq.finalDeliverystamp), 112) >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0)
        AND CONVERT(varchar(50), dbo.EpochToLocal(rq.finalDeliverystamp), 112) < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
)
SELECT 
    pt.panelTestKey,
    p.orgChartNumber,
    t.name AS 'test',
    r.result
FROM mytables rq
JOIN pt ON rq.关联字段 = pt.关联字段
JOIN p ON rq.关联字段 = p.关联字段
JOIN t ON rq.关联字段 = t.关联字段
JOIN r ON rq.关联字段 = r.关联字段
JOIN PatientStatus ps ON p.orgChartNumber = ps.orgChartNumber
WHERE 
    pt.panelTestKey IN (133, 2154, 2157)
    AND pt.isnonreportable = 0
    AND valuetoReport = 1
    AND ps.IsEligible = 1
    -- 保留日期筛选逻辑
    AND CONVERT(varchar(50), dbo.EpochToLocal(rq.finalDeliverystamp), 112) >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0)
    AND CONVERT(varchar(50), dbo.EpochToLocal(rq.finalDeliverystamp), 112) < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

注意事项 #

• 必须补充原查询中缺失的表关联条件（替换代码中的关联字段为实际表关联字段），否则查询会因表关联不明确报错
• 原查询中直接从mytables rq查询，但SELECT引用了pt、p、t、r的字段，说明这些表需要与rq关联，务必补充完整JOIN逻辑

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