问题描述
我有这个sql代码:
SELECT NoteOID = HCN.ObjectID, PatientOID = HCN.PatientID, PatientVisitOID = 0, CollectedDT = HCN.CollectedDT FROM HClinicalNote HCN WITH(NOLOCK) where HCN.enddt is NULL and HCN.visitid in (select distinct visitOID from @tblCensus) order by HCN.PatientID,HCN.CollectedDT desc
给出这些结果:
NoteOID PatientOID CollectedDT 181382 890855 2011-09-14 21:31:00 169115 890855 2011-09-12 18:38:00 177466 890855 2011-09-09 19:49:00 175150 890855 2011-09-07 19:34:00 174057 890855 2011-09-06 19:25:00 172429 890855 2011-09-04 09:00:00 181387 13462666 2011-09-14 21:37:00 182224 13462666 2011-09-14 13:24:00 179269 13462666 2011-09-12 18:12:00
我想从每组 PatientOID 中获得前 2 个 CollectedDT.
I would like to have the top 2 CollectedDT from each group of PatientOID.
推荐答案
如果您至少使用 SQL-Server 2005,则可以使用 CTE 和 ROW_NUMBER 函数:
If you're using at least SQL-Server 2005, you ca use a CTE with ROW_NUMBER function:
WITH CTE AS( SELECT NoteOID = HCN.ObjectID, PatientOID = HCN.PatientID, PatientVisitOID = 0, CollectedDT = HCN.CollectedDT, RN = ROW_NUMBER()OVER(PARTITION BY PatientOID ORDER BY CollectedDT ASC) FROM HClinicalNote HCN WHERE HCN.enddt is NULL AND HCN.visitid in (select distinct visitOID from @tblCensus) ) SELECT * FROM CTE WHERE RN <= 2