T-SQL - 通过最近日期选择并按ID分组

Question

T-SQL - 通过最近日期选择并按ID分组

sqlsql-serversql-server-2005t-sql

5

我需要使用 SQL Server 2005 从以下数据中选择每个 Linked ID 最接近指定日期的记录：

ID     Date      Linked ID
...........................
1    2010-09-02     25
2    2010-09-01     25
3    2010-09-08     39
4    2010-09-09     39
5    2010-09-10     39
6    2010-09-10     34
7    2010-09-29     34
8    2010-10-01     37
9    2010-10-02     36
10   2010-10-03     36

因此，使用 01/10/2010 进行选择应返回：

1    2010-09-02     25 
5    2010-09-10     39
7    2010-09-29     34 
8    2010-10-01     37
9    2010-10-02     36

我知道这一定是可能的，但似乎我无法理解它（可能是因为接近一天结束了:P）。如果有人能够帮助或给我一个温和的推动方向，那将不胜感激！ 编辑：此外，我已经找到了这个sql以获取最近的日期：

abs(DATEDIFF(minute, Date_Column, '2010/10/01'))

但是我无法弄清楚如何正确地将其纳入查询中...

谢谢

- Iain Ward

在我的评论后，您添加了...。好的，我会给您写完整的查询。 - Hogan

你的标题有误导性，可能应该是“按最近日期选择ID排序”。 - dvhh

您在使用minute作为datediff的第一个参数时可能无法正常运行（因为您在示例日期中没有时间）。您应该使用day - 可以缩写为 day、dd 或 d。有趣的是，所有答案都使用了不同的缩写。 - Hogan

@dvhh 不，我不这么认为。我正在描述我尝试做的事情，如果你看一下Hogan的答案，那正是他所做的。 - Iain Ward

3个回答

4

您想查看DATEDIFF函数（http://msdn.microsoft.com/en-us/library/ms189794.aspx）的绝对值，以天为单位。

查询可以类似于以下内容（未经测试）：

with absDates as 
(
   select *, abs(DATEDIFF(day, Date_Column, '2010/10/01')) as days
   from table
), mdays as
( 
   select min(days) as mdays, linkedid
   from absDates
   group by linkedid
)
select * 
from absdates
inner join mdays on absdays.linkedid = mdays.linkedid and absdays.days = mdays.mdays

- Hogan

@Hogan 是的，我已经遇到过了，但是忘记在问题中提到了，所以我更新了它。谢谢你提醒。 - Iain Ward

@w69rdy：添加了示例查询。 - Hogan

示例查询可能不正确--应该是min(days)，否则您将返回最大差异，对吧？此外，我认为这样的性能并不好。一般来说，我建议使用ROW_NUMBER()解决方案。它应该更直接和更高效。 - Sean Reilly

@Hogan，你的查询目前失败了。表absdays在哪里定义？ - bobs

@bobs：不是:D，我修复了拼写错误。 - Hogan

显示剩余5条评论

0

你也可以尝试在select语句中使用子查询来完成：

select  [LinkedId],
        (select top 1 [Date] from [Table] where [LinkedId]=x.[LinkedId] order by abs(DATEDIFF(DAY,[Date],@date)))
from    [Table] X
group by [LinkedId]

- pcofre

哇，这绝对是最低效的——每个linkedid都要进行一次选择。 - Hogan

是的，这只是针对小表格的。 - pcofre

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- bobs · Accepted Answer

你可以尝试这个。

DECLARE @Date DATE = '10/01/2010';

WITH cte AS
    (
    SELECT ID, LinkedID, ABS(DATEDIFF(DD, @date, DATE)) diff,
        ROW_NUMBER() OVER (PARTITION BY LinkedID ORDER BY ABS(DATEDIFF(DD, @date, DATE))) AS SEQUENCE
    FROM MyTable
    )

SELECT *
FROM cte
WHERE SEQUENCE = 1
ORDER BY ID
;

您没有说明在LinkedID组中多行表示最接近目标日期的情况下如何处理。该解决方案将只包括一行，而在这种情况下，无法保证包括哪个多个有效值中的行。

如果要包括代表最接近值的所有行，则可以在查询中使用RANK()替换ROW_NUMBER()。