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

 表结构如下

 

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

 

 

原帖地址

:

http://topic.youkuaiyun.com/u/20091109/14/5af0c834-674d-419d-a383-d655c2ee8593.html