按日期选择最晚日期与最早日期对应的数的差值
前端之家收集整理的这篇文章主要介绍了
按日期选择最晚日期与最早日期对应的数的差值，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
表结构如下 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
<div class="codetitle"><a style="CURSOR: pointer" data="88325" class="copybut" id="copybut88325" onclick="doCopy('code88325')"> 代码如下:
<div class="codebody" id="code88325">
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),120) )
) d2
where convert(varchar(10),d1.date,120)=convert(varchar(10),d2.date,120)
