sql - 我希望我可以关联一个 "inline view"

Question

我有一个病人表:

PatientId   Admitted
---------   ---------------
1           d/m/yy hh:mm:ss
2           d/m/yy hh:mm:ss
3           d/m/yy hh:mm:ss

我有一个 PatientMeasurement 表(0 到多个):

PatientId   MeasurementId   Recorded            Value
---------   -------------   ---------------     -----
1           A               d/h/yy hh:mm:ss     100
1           A               d/h/yy hh:mm:ss     200
1           A               d/h/yy hh:mm:ss     300
2           A               d/h/yy hh:mm:ss     10
2           A               d/h/yy hh:mm:ss     20
1           B               d/h/yy hh:mm:ss     1
1           B               d/h/yy hh:mm:ss     2

我正在尝试创建一个类似于以下内容的结果集:

PatientId   Numerator   Denominator
---------   --------    -----------
1           1           1
2           1           1
3           0           1       

本质上，如果患者至少有一个测量值 A 和一个测量值 B，则患者的分子将是 1。在此示例中，患者 1 有 3 个 A 测量值和 2 个 B 测量值，因此分子为 1。 患者2 有 2 个 A 测量值，但没有 B 测量值，因此分子为 0。患者既没有 A 测量值也没有 B 测量值，因此分子为 0。

到目前为止，我的查询是:

SELECT  PatientId, CASE WHEN a.cnt+b.cnt>2 THEN 1 ELSE 0 END Numerator, 1 Denominator
FROM    patient p

LEFT OUTER JOIN (
    SELECT  PatientId, count(*) cnt
    FROM    PatientMeasurement pm
    WHERE   MeasurementId='A'
    --AND   Recorded <= dateadd(hh, 12, Admitted)
    GROUP BY PatientId
) a ON p.PatientId=a.PatientId

LEFT OUTER JOIN (
    SELECT  PatientId, count(*) cnt
    FROM    PatientMeasurement pm
    WHERE   MeasurementId='B'
    --AND   Recorded <= dateadd(hh, 12, Admitted)
    GROUP BY PatientId
) b ON p.PatientId=b.PatientId

只要我不包括相关的日期限制 (Recorded < dateadd(hh, 12, Admitted)，这就会按预期工作。不幸的是，以这种方式关联“内联 View ”在语法上是无效的。

这迫使我将 SQL 重写为:

SELECT  PatientId, CASE WHEN v.a+v.b>2 THEN 1 ELSE 0 END Numerator, 1 Denominator
FROM    (

    SELECT  PatientId,
    (
        SELECT  PatientId, count(*) cnt
        FROM    PatientMeasurement pm
        WHERE   PatientId=p.PatientId
        AND MeasurementId='A'
        AND Recorded <= dateadd(hh, 12, Admitted)
        GROUP BY PatientId
    ) a,
    (
        SELECT  PatientId, count(*) cnt
        FROM    PatientMeasurement pm
        WHERE   PatientId=p.PatientId
        AND MeasurementId='B'
        AND Recorded <= dateadd(hh, 12, Admitted)
        GROUP BY PatientId
    ) b
    FROM    Patient p
) v

我的问题:有没有更好、更有效的方法来做到这一点？

谢谢你的时间。

Answer 1

尝试这个 :

WITH GroupPatients AS 
    (SELECT MeasurementID, PatientId, Count(*) AS cnt
    FROM PatientMeasurement AS pm
    INNER JOIN Patient p ON pm.PatientID = p.PatientID
    WHERE
        MeasurementId IN ('A', 'B')
    AND
        Recorded <= dateadd(hh, 12, Admitted)
    GROUP BY MeasureMentID, PatientId)

SELECT p.PatientID, Case
    When IsNull(GPA.cnt, 0) > 0 AND IsNull(GPB.cnt, 0) > 0 Then 1
    Else 0
End AS Numerator, 1 AS Denominator
FROM Patient p
LEFT JOIN GroupPatientsA AS GPA ON p.PatientID = GPA.PatientID AND GPA.MeasurementID = 'A'
LEFT JOIN GroupPatientsB AS GPB ON p.PatientID = GPB.PatientID AND GPB.MeasurementID = 'B'

我也对业务逻辑做了一个调整——你的规范说如果病人同时有 A 和 B 测量值，分子应该是一个——但是，如果 a.cnt+b.cnt>2 的子句将错误地返回一个.cnt 或 b.cnt 为 3 或更多，另一个为零。