求一sql语句

[inrie]

有一个表table1,包含两个字段,如下

id date1
1 2004-02-02
2 2004-02-15
3 2004-02-18
4 2004-02-03
5 2004-03-05
6 2004-03-11
7 2004-04-09
... ........

怎样才能找出date1字段中相邻的两个记录间时间间隔最长的那两个记录.(date1字段存日期).谢谢!

[hsj20041004]

你找出一个最大的date1
再找出一个最小的date1
俩个相差的时间当然最大了
select * from table1 where date1=(select max(date1) from table1)
select * from table1 where date1=(select min(date1) from table1)

[wyb0026]

mark

[LBYYBL]

--测试数据
create table table1(id int identity(1,1),date1 datetime)
insert into table1(date1) select '2004-02-02'
insert into table1(date1) select '2004-02-15'
insert into table1(date1) select '2004-02-18'
insert into table1(date1) select '2004-02-03'
insert into table1(date1) select '2004-03-05'
insert into table1(date1) select '2004-03-11'
insert into table1(date1) select '2004-04-09'

--sql语句
select top 2 * from table1 where id>=
(select top 1 a.id from table1 a join table1 b on a.id=b.id-1 order by abs(datediff(day,a.date1,b.date1)) desc)
order by id

[wyb0026]

楼上写比我快佩服

[子陌红尘]

create table1(id int,date1 datetime)
insert into table1 select 1,'2004-02-02'
insert into table1 select 2,'2004-02-15'
insert into table1 select 3,'2004-02-18'
insert into table1 select 4,'2004-02-03'
insert into table1 select 5,'2004-03-05'
insert into table1 select 6,'2004-03-11'
insert into table1 select 7,'2004-04-09'



select
a.*
from
table1 a,
table1 b
where
abs(a.id-b.id) = 1
and
abs(datediff(day,a.date1,b.date1)) =
(select
max(datediff(day,a.date1,b.date1))
from
table1 a,
table1 b
where
a.id = b.id - 1)