如何在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 ）

推荐答案

测试它： 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