按日期选择最晚日期与最早日期对应的数的差值

原作者: [db:作者] 来自: [db:来源] 收藏邀请

表结构如下

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) 