如何在sql中查找下一个大于Date的内容 | 如何在sql中查找下一个大于Date的内容

本文介绍了如何在sql中查找下一个大于Date的内容的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

<big>patientno	VisitDate	NextVisitDate</big>
210	1/1/2001	5/1/2001
210	5/1/2001	9/1/2001
210	9/1/2001	NotFound
211	1/1/2001	5/1/2001
211	5/1/2001	9/1/2001
211	9/1/2001	NotFound

i希望显示当前访问日期的每位患者的下次访问日期

i只有两列1，patientno，2nd visitdate how显示nextvisitdate

从医院选择Patientno，visitdate（ nextvisitdate ）

推荐答案

DECLARE @tbl TABLE (PatientNo INT, VisitDate DATETIME)

INSERT INTO @tbl (PatientNo, VisitDate)
VALUES(210, '1/1/2001'),
(210, '5/1/2001'),
(210, '9/1/2001'),
(211, '1/1/2001'),
(211, '5/1/2001'),
(211, '9/1/2001')


SELECT t1.PatientNo, t1.Rowno, MAX(t1.VisitDate) AS VisitDate, MAX(t2.VisitDate) AS NextVisitDate
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY PatientNo ORDER BY PatientNo, VisitDate) AS RowNo, PatientNo, VisitDate
    FROM @tbl
    ) AS t1 LEFT JOIN (
        SELECT ROW_NUMBER() OVER(PARTITION BY PatientNo ORDER BY PatientNo, VisitDate) -1 AS RowNo, PatientNo, VisitDate
        FROM @tbl
        ) AS t2 ON t1.RowNo = t2.RowNo
GROUP BY t1.PatientNo, t1.RowNo
ORDER BY t1.PatientNo, t1.RowNo

SELECT patientno, visitdate, lead(visitdate, 1, null) over (PARTITION BY patientno ORDER BY visitdate)
FROM visits v

这篇关于如何在sql中查找下一个大于Date的内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！