sql question - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

sql question

sql question# Database - 数据库

k*e2006-12-16 08:12

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*n2006-12-16 08:12

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*d2006-12-16 08:12

3 楼

必须一个sql statement?

【在 k***e 的大作中提到】

O*K2006-12-16 08:12

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*n2006-12-16 08:12

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