您可以使用OUTER APPLY。它就像一个相关的子查询,但是允许多个列:
SELECt p.ID, p.Patient, p.ProcedureType, p.ProcedureDate, [LastExamDate] = exam.ProcedureDate, [DaysSinceLastExam] = DATEDIFF(DAY, exam.ProcedureDate, p.ProcedureDate), [LastExamType] = exam.ProcedureType FROM Procedures p OUTER APPLY ( SELECt TOP 1 exams.ProcedureType, exams.ProcedureDate FROM Procedures exams WHERe Exams.ProcedureType LIKE '%Exam%' AND Exams.Patient = p.Patient AND Exams.ProcedureDate <= p.ProcedureDate ORDER BY Exams.ProcedureDate DESC ) exam;
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)