相思资源网 Design By www.200059.com
表结构如下
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
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
标签：
日期差值
相思资源网 Design By www.200059.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
相思资源网 Design By www.200059.com
暂无按日期选择最晚日期与最早日期对应的数的差值的评论...