SQLServer时间分段查询

统计连续时间段数据

if OBJECT_ID(N'Test',N'U') is not null
	drop table Test
go 

create table Test(
	pscode decimal(15),outputcode int,monitortime datetime
)

insert into Test
select 4100000406,1,convert(datetime,'2015-04-01 00:00') union all
select 4100000406,'2015-04-01 01:00') union all
select 4100000406,'2015-04-01 02:00') union all
select 4100000406,'2015-04-01 03:00') union all
select 4100000406,'2015-04-01 04:00') union all
select 4100000406,'2015-04-01 05:00') union all
select 4100000406,'2015-04-01 06:00') union all
select 4100000406,'2015-04-01 07:00') union all
select 4100000406,'2015-04-01 08:00') union all
select 4100000406,'2015-04-01 09:00') union all
select 4100000406,'2015-04-01 10:00') union all
select 4100000406,'2015-04-01 11:00') union all
select 4100000406,'2015-04-01 13:00') union all
select 4100000406,'2015-04-01 14:00') union all
select 4100000406,'2015-04-01 15:00') union all
select 4100000406,'2015-04-01 16:00') union all
select 4100000406,'2015-04-01 17:00') union all
select 4100000406,'2015-04-01 18:00') union all
select 4100000406,'2015-04-01 19:00') union all
select 4100000406,'2015-04-01 20:00') union all
select 4100000406,'2015-04-01 22:00') union all
select 4100000406,'2015-04-01 23:00') union all
select 4100000405,2,'2015-04-01 01:00') union all
select 4100000405,'2015-04-01 02:00') union all
select 4100000405,'2015-04-01 03:00') union all
select 4100000405,'2015-04-01 04:00') union all
select 4100000405,'2015-04-01 05:00') union all
select 4100000405,'2015-04-01 06:00') union all
select 4100000405,'2015-04-01 07:00') union all
select 4100000405,'2015-04-01 08:00') union all
select 4100000405,'2015-04-01 09:00') union all
select 4100000405,'2015-04-01 11:00') union all
select 4100000405,'2015-04-01 12:00') union all
select 4100000405,'2015-04-01 13:00') union all
select 4100000405,'2015-04-01 14:00') union all
select 4100000405,'2015-04-01 15:00') union all
select 4100000405,'2015-04-01 16:00') union all
select 4100000405,'2015-04-01 17:00') union all
select 4100000405,'2015-04-01 18:00') union all
select 4100000402,'2015-04-01 00:00') union all
select 4100000402,'2015-04-01 01:00') union all
select 4100000402,'2015-04-01 02:00') union all
select 4100000402,'2015-04-01 03:00') union all
select 4100000402,'2015-04-01 04:00') union all
select 4100000402,'2015-04-01 05:00') union all
select 4100000402,'2015-04-01 06:00') union all
select 4100000402,'2015-04-01 07:00') union all
select 4100000402,'2015-04-01 08:00') union all
select 4100000402,'2015-04-01 09:00') union all
select 4100000402,'2015-04-01 11:00') union all
select 4100000402,'2015-04-01 12:00') union all
select 4100000402,'2015-04-01 13:00') union all
select 4100000402,'2015-04-01 14:00') union all
select 4100000402,'2015-04-01 15:00') union all
select 4100000402,'2015-04-01 16:00') union all
select 4100000402,'2015-04-01 17:00') union all
select 4100000402,'2015-04-01 19:00') union all
select 4100000402,'2015-04-01 20:00') union all
select 4100000402,'2015-04-01 22:00') union all
select 4100000402,'2015-04-01 23:00')


select pscode,outputcode,convert(varchar(16),MIN(monitortime),120)+'～'+convert(varchar(16),MAX(monitortime),120) fw,COUNT(1) num
  from (select x.pscode,x.outputcode,x.monitortime,dateadd(HOUR,-x.orderby,x.monitortime) diff
		  from (select pscode,monitortime,ROW_NUMBER() over(partition by pscode,outputcode order by pscode,monitortime) orderby 
				  from Test) x)y 
 group by y.pscode,y.outputcode,y.diff

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!