Access查询需求：按PatientID计算最近两次Value的差值

问题背景 #

现有Access表GAD7，包含PatientID、TestDate、Score字段（注：原问题描述中字段名为Date、Value，与提供的SQL语句字段名TestDate、Score不一致，以下以SQL语句中的字段名为准）。需筛选出拥有2条及以上记录的患者，计算其最近两次评分的差值（最后一次Score减去倒数第二次Score）。现有SQL已完成筛选并获取最后一次的日期和评分，但无法获取倒数第二次的数据。

现有SQL #

SELECT GAD7.PatientID, Count(GAD7.PatientID) AS CountOfPatientID, Last(GAD7.TestDate) AS LastDate, Last(GAD7.Score) AS LastScore
FROM GAD7
GROUP BY GAD7.PatientID
HAVING (((Count(GAD7.PatientID))>=2))
ORDER BY GAD7.PatientID;

解决方案 #

可以通过子查询给记录编号的方式，提取每个患者最新的两条记录，再计算差值，具体SQL如下：

SELECT 
    sub.PatientID,
    COUNT(sub.PatientID) AS CountOfPatientID,
    MAX(IIF(sub.RecordRank=1, sub.TestDate, NULL)) AS LastDate,
    MAX(IIF(sub.RecordRank=1, sub.Score, NULL)) AS LastScore,
    MAX(IIF(sub.RecordRank=2, sub.TestDate, NULL)) AS SecondLastDate,
    MAX(IIF(sub.RecordRank=2, sub.Score, NULL)) AS SecondLastScore,
    (MAX(IIF(sub.RecordRank=1, sub.Score, NULL)) - MAX(IIF(sub.RecordRank=2, sub.Score, NULL))) AS ScoreDiff
FROM (
    -- 内层子查询：给每个患者的记录按日期从新到旧编号
    SELECT 
        PatientID,
        TestDate,
        Score,
        (SELECT COUNT(*) FROM GAD7 AS g2 
         WHERE g2.PatientID = g1.PatientID AND g2.TestDate >= g1.TestDate) AS RecordRank
    FROM GAD7 AS g1
) AS sub
WHERE sub.RecordRank <= 2
GROUP BY sub.PatientID
HAVING COUNT(sub.PatientID) >= 2
ORDER BY sub.PatientID;

语句说明 #

1. 内层子查询sub：给每个患者的记录按TestDate降序编号，最新的记录编号为1，倒数第二次为2。
2. 外层查询：通过IIF函数分别提取排名1和2的日期、评分，再计算两者的差值ScoreDiff。
3. HAVING子句确保仅保留有2条及以上记录的患者。

另一种实现方式（自连接） #

如果偏好自连接写法，也可以通过两次子查询分别获取最新和倒数第二次记录，再关联计算：

SELECT 
    g.PatientID,
    COUNT(g.PatientID) AS CountOfPatientID,
    last.TestDate AS LastDate,
    last.Score AS LastScore,
    secondLast.TestDate AS SecondLastDate,
    secondLast.Score AS SecondLastScore,
    (last.Score - secondLast.Score) AS ScoreDiff
FROM GAD7 AS g
-- 关联最新记录
INNER JOIN (
    SELECT PatientID, TestDate, Score
    FROM GAD7 AS g1
    WHERE TestDate = (SELECT MAX(TestDate) FROM GAD7 AS g2 WHERE g2.PatientID = g1.PatientID)
) AS last ON g.PatientID = last.PatientID
-- 关联倒数第二次记录
INNER JOIN (
    SELECT PatientID, TestDate, Score
    FROM GAD7 AS g1
    WHERE TestDate = (
        SELECT MAX(TestDate) FROM GAD7 AS g2 
        WHERE g2.PatientID = g1.PatientID AND g2.TestDate < (SELECT MAX(TestDate) FROM GAD7 AS g3 WHERE g3.PatientID = g1.PatientID)
    )
) AS secondLast ON g.PatientID = secondLast.PatientID
GROUP BY g.PatientID, last.TestDate, last.Score, secondLast.TestDate, secondLast.Score
HAVING COUNT(g.PatientID) >= 2
ORDER BY g.PatientID;

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