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

225 阅读 0 评论 149 点赞

我是靠谱客的博主坦率咖啡豆，最近开发中收集的这篇文章主要介绍按日期选择最晚日期与最早日期对应的数的差值，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

表结构如下

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) 