带有最年轻的DateStamp的SQL在左外接中的子选择

Question

在Patient上可以有一个或多个PrmryDiagnosis，数据库中的每个Diagnosis也有一个DateStamp。所以，我只想得到最新的PrmryDiagnosis，如果有更多的PrmryDiagnosis比一个。

我已经在我的代码中得到了一堆左外部联接，在子查询中带有max-子句的这个代码片段没有将Diagnosis加入到我的表中！它选择正确的一个，但不加入我的新列诊断！请你帮帮我！诚挚的问候!

/*code snippets*/

SELECT di.DiagnosisCode,
       di.Description,
       di.DiagnosisType
       di.PatientSer
FROM dbo.Activity,dbo.ActivityInstance,dbo.ScheduledActivity sa 
LEFT OUTER JOIN dbo.Patient pa ON sa.PatientSer = pa.PatientSer 
LEFT OUTER JOIN dbo.Diagnosis di ON(sa.PatientSer = di.PatientSer AND   
                                    di.DiagnosisType='PrmryDiagnosis' AND
                                    di.ObjectStatus='Active' AND 
                                    di.DateStamp = (Select MAX(DateStamp) 
                                                    from Diagnosis 
                                                    where di.PatientSer = sa.PatientSer))                                                                                                       

WHERE 
    (YEAR(sa.ScheduledStartTime) = YEAR(CURRENT_TIMESTAMP)) AND  
    (MONTH(sa.ScheduledStartTime) = MONTH(CURRENT_TIMESTAMP)) AND  
    (DAY(sa.ScheduledStartTime) = DAY(CURRENT_TIMESTAMP))

Answer 1

考虑使用row_number()，如这里所示，以获得“最近的记录”

SELECT
      di.DiagnosisCode
    , di.Description
    , di.DiagnosisType
    , di.PatientSer
FROM (
            SELECT
                  DiagnosisCode
                , Description
                , DiagnosisType
                , PatientSer
                , ROW_NUMBER() OVER (PARTITION BY PatientSer
                                     ORDER BY DateStamp DESC) AS rn
            FROM dbo.Diagnosis
            WHERE DiagnosisType = 'PrmryDiagnosis'
                  AND ObjectStatus = 'Active'
      ) di
WHERE rn = 1;