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 的大作中提到】
: 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 的大作中提到】
: 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 的大作中提到】
: 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
|
|
