当前位置：首页 -> 基础 -> 数据库编程

SQLServer时间分段查询(一)

2015-07-21 16:27:45 · 作者: · 浏览: 22
标签: 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,1,convert(datetime,'2015-04-01 01:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 02:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 03:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 04:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 05:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 06:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 07:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 08:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 09:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 10:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 11:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 13:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 14:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 15:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 16:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 17:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 18:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 19:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 20:00') union all
select 4100000






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