T-SQL - 获取最近的日期和最近的未来日期

2021-09-10 00:00:00 tsql sql-server

假设下面的记录表

ID    Name       AppointmentDate
--    --------   ---------------
1     Bob         1/1/2010
1     Bob         5/1/2010
2     Henry       5/1/2010
2     Henry       8/1/2011
3     John        8/1/2011
3     John       12/1/2011

我想按人检索最近的约会日期.所以我需要一个查询来提供以下结果集.

I want to retrieve the most recent appointment date by person. So I need a query that will give the following result set.

1   Bob    5/1/2010 (5/1/2010 is most recent)
2   Henry  8/1/2011 (8/1/2011 is most recent)
3   John   8/1/2011 (has 2 future dates but 8/1/2011 is most recent)

谢谢！

推荐答案

假设您说最近"的意思是最近"，例如存储的日期是距当前日期最少的天数，而我们不这样做"不关心它是在当前日期之前还是之后"，那么应该这样做(可能需要进行琐碎的调试):

Assuming that where you say "most recent" you mean "closest", as in "stored date is the fewest days away from the current date and we don't care if it's before or after the current date", then this should do it (trivial debugging might be required):

SELECT ID, Name, AppointmentDate
 from (select
           ID
          ,Name
          ,AppointmentDate
          ,row_number() over (partition by ID order by abs(datediff(dd, AppointmentDate, getdate()))) Ranking
         from MyTable) xx
 where Ranking = 1

这使用 SQL 2005 及更高版本的 row_number() 函数.子查询根据规范对数据进行排序"，主查询选择最合适的.

This usese the row_number() function from SQL 2005 and up. The subquery "orders" the data as per the specifications, and the main query picks the best fit.

还要注意:

  • 搜索基于当前日期
  • 我们只计算天数差异，忽略时间(小时、分钟等)
  • 如果两天是等距的(比如之前 2 天和之后 2 天)，我们随机选择一个

所有这些都可以根据您的最终要求进行调整.

All of which could be adjusted based on your final requirements.

相关文章