对于表患者和实验室
patient
id lastname
19 patientone
20 patienttwo
patientid lastname loinc datetime numerical
19 patientone 4548-4 2014-05-15 00:00:00 6.5
19 patientone 4548-4 2015-05-15 00:00:00 7.5
19 patientone 4548-4 2016-05-15 00:00:00 3.5
19 patientone 4548-4 2017-05-15 00:00:00 5.5
19 patientone 5000-3 2018-05-15 00:00:00 123
20 patienttwo 4548-4 2013-05-15 00:00:00 2.5
20 patienttwo 4548-4 2012-05-15 00:00:00 1.5
20 patienttwo 4548-4 2011-05-15 00:00:00 9.5
20 patienttwo 4548-4 2010-05-15 00:00:00 3.5
Desired output:
patientid lastname datetime numerical
19 patientone 2017-05-15 00:00:00 5.5
20 patienttwo 2013-05-15 00:00:00 2.5
labh 表保存实验室值(数字)、实验室类型(loinc)以及完成时间(日期时间)。我想查询 loinc=4548-4 的最新值,我希望输出显示日期和值。
我在下面试过这个,它显示了最近的日期,但我无法同时看到值(数字)。当我添加数字列时,它会显示所有值,而不仅仅是最近的值。
Select Distinct patient.id,patient.lastname,Max(Date_Trunc('day',labh.datetime)) As "Date" From patient Inner Join labh On patient.id = labh.patientid Where labh.loinc = '4548-4' Group By patient.id,patient.firstname Order By patient.id