我正在处理一个查询,其中需要查看患者去诊所时输入的患者生命体征(特别是血压)。我将获得2015年全年的结果,当然,某些患者曾多次就诊,我只需要查看最近一次就诊时输入的生命指标即可。另一个轻微的变化是收缩压和舒张压是分别输入的,因此我得到如下结果:
Patient ID Name DOB Test Results Date
---------------------------------------------------------------------------------
1000 John Smith 1/1/1955 BP - Diastolic 120 2/10/2015
1000 John Smith 1/1/1955 BP - Systolic 70 2/10/2015
1000 John Smith 1/1/1955 BP - Diastolic 128 7/12/2015
1000 John Smith 1/1/1955 BP - Systolic 75 7/12/2015
1000 John Smith 1/1/1955 BP - Diastolic 130 10/22/2015
1000 John Smith 1/1/1955 BP - Systolic 76 10/22/2015
9999 Jane Doe 5/4/1970 BP - Diastolic 130 4/2/2015
9999 Jane Doe 5/4/1970 BP - Systolic 60 4/2/2015
9999 Jane Doe 5/4/1970 BP - Diastolic 127 11/20/2015
9999 Jane Doe 5/4/1970 BP - Systolic 65 11/20/2015
有26,000多个结果,因此显然我不想遍历每位患者并查看他们的最新结果是什么时候。我希望我的结果看起来像这样:
Patient ID Name DOB Test Results Date
---------------------------------------------------------------------------------
1000 John Smith 1/1/1955 BP - Diastolic 130 10/22/2015
1000 John Smith 1/1/1955 BP - Systolic 76 10/22/2015
9999 Jane Doe 5/4/1970 BP - Diastolic 127 11/20/2015
9999 Jane Doe 5/4/1970 BP - Systolic 65 11/20/2015
我知道出生的名字和日期,以后不会重复,但是我主要关注结果栏。
这是我的查询:
SELECT DISTINCT
pd.PatientID as [Patient ID],
pd.PatientName as Name,
pd.DateOfBirth as DOB,
v.Test as Test,
v.Results as Results,
v.TestDate as Date
FROM PatientDemographic pd JOIN Vitals v ON pd.PatientID = v.PatientID
WHERE v.TestDate BETWEEN '01/01/2015' AND '12/31/2015'
AND v.Test LIKE 'BP%'
ORDER BY pd.PatientID, v.TestDate
在寻找其他答案之后,我尝试对语句中的列执行GROUP BY
和的MAX()
聚合函数(我专门引用了此链接,尽管它是针对Oracle的,并且我正在使用SQL Server,所以我不确定语法是否完全正确。会是一样的)。然后,我的查询如下所示:v.TestDate
SELECT
SELECT DISTINCT
pd.PatientID as [Patient ID],
pd.PatientName as Name,
pd.DateOfBirth as DOB,
v.Test as Test,
v.Results as Results,
MAX(v.TestDate) as Date
FROM PatientDemographic pd JOIN Vitals v ON pd.PatientID = v.PatientID
WHERE v.TestDate BETWEEN '01/01/2015' AND '12/31/2015'
AND v.Test LIKE 'BP%'
GROUP BY pd.PatientID
诚然,我一直在使用方面有些挣扎GROUP BY
。在这种特殊情况下,我收到一条错误消息,指出我也需要在该GROUP BY
子句中添加“患者姓名”列,所以我这样做了,然后它要求提供DOB。然后是测试名称。基本上,它希望我将所有内容从SELECT
语句添加到中GROUP BY
。
进行我最近的患者就诊的最佳方法是什么?
一种简单的方法用于ROW_NUMBER()
查找每个测试的最新记录:
SELECT pd.PatientID as [Patient ID], pd.PatientName as Name, pd.DateOfBirth as DOB,
v.Test as Test, v.Results as Results, v.TestDate as Date
FROM PatientDemographic pd JOIN
(SELECT v.*,
ROW_NUMBER() OVER (PARTITION BY PatientId, Test ORDER BY TestDate DESC) as seqnum
FROM Vitals v
WHERE v.TestDate BETWEEN '2015-01-01' AND '2015-12-31' AND
v.Test LIKE 'BP%'
) v
ON pd.PatientID = v.PatientID
WHERE seqnum = 1
ORDER BY pd.PatientID, v.TestDate;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句