SqlServer根据时段统计数据

create table ST_PPTN_R_Real
(
TID int primary key identity(1,1),
STCD varchar(100) not null,
TM datetime not null,
DRP decimal(8,2) 
)
go
 
insert into ST_PPTN_R_Real values('龙谷站点','2013-05-01 00:00:00.000','52.3')
insert into ST_PPTN_R_Real values('龙谷站点','2013-05-01 00:35:00.000','77.3')
insert into ST_PPTN_R_Real values('龙谷站点','2013-05-01 00:44:00.000','46.4')
insert into ST_PPTN_R_Real values('龙谷站点','2013-05-02 15:21:00.000','85.5')
insert into ST_PPTN_R_Real values('龙谷站点','2013-05-03 05:16:00.000','42.3')
insert into ST_PPTN_R_Real values('龙谷站点','2013-05-03 22:31:55.000','160.77')
insert into ST_PPTN_R_Real values('奇峰峰巅','2013-05-01 02:41:00.000','23.3')
insert into ST_PPTN_R_Real values('奇峰峰巅','2013-05-03 14:50:00.000','564.3')
insert into ST_PPTN_R_Real values('奇峰峰巅','2013-05-05 22:33:00.000','77.5')
insert into ST_PPTN_R_Real values('枉顾站地','2013-05-03 02:01:00.000','43.3')
insert into ST_PPTN_R_Real values('枉顾站地','2013-05-03 05:21:00.000','67.3')
insert into ST_PPTN_R_Real values('枉顾站地','2013-05-06 14:42:00.000','56.4')
insert into ST_PPTN_R_Real values('枉顾站地','2013-05-06 15:21:00.000','83.5')
insert into ST_PPTN_R_Real values('柳体站点','2013-05-14 07:00:00.000','34.3')
insert into ST_PPTN_R_Real values('柳体站点','2013-05-15 09:35:00.000','23.3')
insert into ST_PPTN_R_Real values('柳体站点','2013-05-16 11:44:00.000','62.4')
insert into ST_PPTN_R_Real values('柳体站点','2013-05-16 18:44:00.000','73.4')
insert into ST_PPTN_R_Real values('柳体站点','2013-05-17 09:44:00.000','66.4')
go
 
select * from ST_PPTN_R_Real
go

（一）首先，把条件表准备好：
1.按照每时统计（其中master..spt_values等于master.dbo.spt_values为系统表，number为其中的子段，其实还有更方便的小时统计分组，但这里都套用了一个思路，就直接写下来了）
 
select CONVERT(varchar(20),dateadd(hour,number,cast('2013-04-01 00:00:00' as datetime)),120) rq,
cast(convert(varchar,dateadd(hour,number,cast('2013-04-01 00:00:00' as datetime)),120) as datetime) begin_time,
cast(convert(varchar,dateadd(hour,number+1,cast('2013-04-01 00:00:00' as datetime)),120) as datetime) end_time
from master..spt_values where type='P'

------这里才是真正的重点:
2.按照每天统计（这里的天与所理解的天不太一样，是根据时段设定的天，例如这里从本日的8点开始到次日的8点结束为一天）
 
select convert(varchar(10),cast('2013-04-01 00:00:00' as datetime)+number,120) rq,
cast(replace(convert(varchar,dateadd(d,0,cast('2013-04-01 00:00:00' as datetime)+number),120),'00:00:00','08:00:00') as datetime) begin_time,
cast(replace(convert(varchar,dateadd(d,1,cast('2013-04-01 00:00:00' as datetime)+number),120),'00:00:00','08:00:00') as datetime) end_time
from master..spt_values where type='P'

3.按照每月统计（其实还有更方便的月统计分组，但这里都套用了一个思路，就直接写下来了）
select CONVERT(varchar(7),dateadd(MONTH,number,cast('2013-04-01 00:00:00' as datetime)),120) rq,
cast(convert(varchar,dateadd(MONTH,number,cast('2013-04-01 00:00:00' as datetime)),120) as datetime) begin_time,
cast(convert(varchar,dateadd(MONTH,number+1,cast('2013-04-01 00:00:00' as datetime)),120) as datetime) end_time
from master..spt_values where type='P'

（二）开始分组统计
 
1.按照每时统计


select b.STCD,a.rq,sum(b.DRP) hj from
(
select CONVERT(varchar(20),dateadd(hour,number,cast('2013-04-01 00:00:00' as datetime)),120) rq,
cast(convert(varchar,dateadd(hour,number,cast('2013-04-01 00:00:00' as datetime)),120) as datetime) begin_time,
cast(convert(varchar,dateadd(hour,number+1,cast('2013-04-01 00:00:00' as datetime)),120) as datetime) end_time
from master..spt_values where type='P'
) a
inner join
ST_PPTN_R_Real b
on
b.TM>=a.begin_time and b.TMgroup by STCD,rq order by STCD,rq desc

2.按照每天统计


select STCD,a.rq,sum(b.DRP) hj from
(
select convert(varchar(10),cast('2013-04-01 00:00:00' as datetime)+number,120) rq,
cast(replace(convert(varchar,dateadd(d,0,cast('2013-04-01 00:00:00' as datetime)+number),120),'00:00:00','08:00:00') as datetime) begin_time,
cast(replace(convert(varchar,dateadd(d,1,cast('2013-04-01 00:00:00' as datetime)+number),120),'00:00:00','08:00:00') as datetime) end_time
from master..spt_values where type='P'
) a
inner join
ST_PPTN_R_Real b
on
b.TM>=a.begin_time and b.TMgroup by STCD,rq order by STCD,rq desc

3.按照每月统计


select b.STCD,a.rq,sum(b.DRP) hj from
(
select CONVERT(varchar(7),dateadd(MONTH,number,cast('2013-04-01 00:00:00' as datetime)),120) rq,
cast(convert(varchar,dateadd(MONTH,number,cast('2013-04-01 00:00:00' as datetime)),120) as datetime) begin_time,
cast(convert(varchar,dateadd(MONTH,number+1,cast('2013-04-01 00:00:00' as datetime)),120) as datetime) end_time
from master..spt_values where type='P'
) a
inner join
ST_PPTN_R_Real b
on
b.TM>=a.begin_time and b.TMgroup by STCD,rq order by STCD,rq desc