如何在sql中查找下一个大于Date的内容 [英] how find next greater than Date in sql

问题描述

<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 ）

推荐答案

测试它：

Test it:

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

我想你想做一个选择NextVisitDate> getdate（），以确保您不会回到过去的下次访问日期，因为人们将来没有访问日期。



没有意义的是，如果你按照病人进行分区，那么按顺序排序，订单应该只是日期。我也不明白为什么你需要一个窗口功能：



选择patientno，max（visitdate），max（visitdate）时的情况> getdate（）then max（visitdate）else null end

来自myTable group by patientNo



是我认为会做的（但我我现在被启动到Mac，所以我无法测试它）。如果这不起作用，我会使用CTE来找到我需要的两个值并从那里开始。

I would imagine you want to do a select where the NextVisitDate > getdate(), to ensure you don't get back 'next visit dates' that are in the past, because people don't have a future visit date.

It makes no sense to order by patientno if you're partitioning by patientno, the order should just be the date. I also don't see why you need a windowing function here:

select patientno, max(visitdate), case when max(visitdate) > getdate() then max(visitdate) else null end
from myTable group by patientNo

is what i think would do it ( but I am booted to Mac right now, so I can't test it ). If that did not work, I'd use CTEs to find the two values I need and go from there.

如果您使用的是SQL Server 2012，这个更好并且工作正常：

If you are using SQL Server 2012, this is much nicer and works fine:

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

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