表结构如下

number date

8 2009/1/11 2:00

7 2009/1/11 5:00

6 2009/1/11 12:00

5 2009/1/11 18:00

4 2009/1/12 4:00

3 2009/1/12 10:00

2 2009/1/12 12:00

1 2009/1/11 17:00

想得到当天的最早时间与最晚时间的number的差值， 即如下的结果:

差

2

3

复制代码 代码如下:

create table #date

(

number int identity(1,1) primary key,

date datetime

)

insert into #date select '2009/1/11 17:00'

insert into #date select '2009/1/12 12:00'

insert into #date select '2009/1/12 10:00'

insert into #date select '2009/1/12 4:00'

insert into #date select '2009/1/11 18:00'

insert into #date select '2009/1/11 12:00'

insert into #date select '2009/1/11 5:00'

insert into #date select '2009/1/11 2:00'

select (d2.number-d1.number) number

from

(

select number,date from #date where date in

(select max(date) from #date group by convert(varchar(10),date,120) )

) d1

,

(

select number,date from #date where date in

(select min(date) from #date group by convert(varchar(10),date,120) )

) d2

where convert(varchar(10),d1.date,120)=convert(varchar(10),d2.date,120)

number

-----------

2

3