k***e 发帖数: 7933 | 1 a table like following:
Name Date
a 12/1/2006
b 12/1/2006
a 12/5/2006
a 12/6/2006
b 12/7/2006
b 12/20/2006
我想对每一个Name,找出Date最接近的两个记录。
上面的例子应该返回
a 12/5/2006
a 12/6/2006
b 12/1/2006
b 12/7/2006
用sql应该如何实现? | l******n 发帖数: 9344 | 2 先单个找出来,然后再比较?
【在 k***e 的大作中提到】![](/moin_static193/solenoid/img/up.png) : a table like following: : Name Date : a 12/1/2006 : b 12/1/2006 : a 12/5/2006 : a 12/6/2006 : b 12/7/2006 : b 12/20/2006 : 我想对每一个Name,找出Date最接近的两个记录。 : 上面的例子应该返回
| c*****d 发帖数: 6045 | 3 必须一个sql statement?
【在 k***e 的大作中提到】![](/moin_static193/solenoid/img/up.png) : a table like following: : Name Date : a 12/1/2006 : b 12/1/2006 : a 12/5/2006 : a 12/6/2006 : b 12/7/2006 : b 12/20/2006 : 我想对每一个Name,找出Date最接近的两个记录。 : 上面的例子应该返回
| O**K 发帖数: 11 | 4 select Name, Date
from (
select top 1 T1.Name, T1.Date, min(T2.Date - T1.Date) as DateDiff
from TableName as T1, TableName as T2
where T1.Name = T2.Name
and T1.Date < T2.Date
group by T1.Name, T1.Date
order by min(T2.Date - T1.Date) ) as A
union
select Name, Date + DateDiff as Date
from (
select top 1 T1.Name, T1.Date, min(T2.Date - T1.Date) as DateDiff
from TableName as T1, TableName as T2
where T1.Name = T2.Name
and T1.Date < T2.Date
group by T1.Name, T1.Date
order by min(T2.Date - T1.Date) ) | w****n 发帖数: 8 | 5 run 的结果是:
a 2006-12-05 00:00:00.000
a 2006-12-06 00:00:00.000
b 呢?
【在 O**K 的大作中提到】![](/moin_static193/solenoid/img/up.png) : select Name, Date : from ( : select top 1 T1.Name, T1.Date, min(T2.Date - T1.Date) as DateDiff : from TableName as T1, TableName as T2 : where T1.Name = T2.Name : and T1.Date < T2.Date : group by T1.Name, T1.Date : order by min(T2.Date - T1.Date) ) as A : union : select Name, Date + DateDiff as Date
|
|