問題描述
假設下面的記錄表
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.
這篇關于T-SQL - 獲取最近的日期和最近的未來日期的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！