日期时间 10分钟雨量
2012-08-17 00:10:00.000 1.3
2012-08-17 00:20:00.000 .0
2012-08-17 00:30:00.000 .0
2012-08-17 00:40:00.000 .0
2012-08-17 00:50:00.000 1.8
2012-08-17 01:00:00.000 .0
2012-08-17 01:10:00.000 .0
2012-08-17 01:20:00.000 .0
2012-08-17 01:30:00.000 .4
2012-08-17 01:40:00.000 .0
2012-08-17 01:50:00.000 4.4
2012-08-17 02:00:00.000 .0
2012-08-17 02:10:00.000 2.2
2012-08-17 02:20:00.000 1.6
2012-08-17 02:30:00.000 .0
2012-08-17 02:40:00.000 .0
2012-08-17 02:50:00.000 .0
2012-08-17 03:00:00.000 .8
2012-08-17 03:10:00.000 1.7
2012-08-17 03:20:00.000 1.3
2012-08-17 03:30:00.000 2.0
2012-08-17 03:40:00.000 2.6
2012-08-17 03:50:00.000 4.3
2012-08-17 04:00:00.000 9.7以上为10分钟雨量统计表,如何输出小时表?
小时统计为 10分钟+20分钟+30分钟+40分钟+50分钟+下1小时的00分钟数据 如2012-08-17 01:00的小时雨量为1.3+0.0+0.0+0.0+1.8+0.0=3.1
小时表结果如下:2012-08-17 01:00:00.000 3.1
2012-08-17 02:00:00.000 4.8
2012-08-17 03:00:00.000 4.6
2012-08-17 04:00:00.000 21.6
2012-08-17 00:10:00.000 1.3
2012-08-17 00:20:00.000 .0
2012-08-17 00:30:00.000 .0
2012-08-17 00:40:00.000 .0
2012-08-17 00:50:00.000 1.8
2012-08-17 01:00:00.000 .0
2012-08-17 01:10:00.000 .0
2012-08-17 01:20:00.000 .0
2012-08-17 01:30:00.000 .4
2012-08-17 01:40:00.000 .0
2012-08-17 01:50:00.000 4.4
2012-08-17 02:00:00.000 .0
2012-08-17 02:10:00.000 2.2
2012-08-17 02:20:00.000 1.6
2012-08-17 02:30:00.000 .0
2012-08-17 02:40:00.000 .0
2012-08-17 02:50:00.000 .0
2012-08-17 03:00:00.000 .8
2012-08-17 03:10:00.000 1.7
2012-08-17 03:20:00.000 1.3
2012-08-17 03:30:00.000 2.0
2012-08-17 03:40:00.000 2.6
2012-08-17 03:50:00.000 4.3
2012-08-17 04:00:00.000 9.7以上为10分钟雨量统计表,如何输出小时表?
小时统计为 10分钟+20分钟+30分钟+40分钟+50分钟+下1小时的00分钟数据 如2012-08-17 01:00的小时雨量为1.3+0.0+0.0+0.0+1.8+0.0=3.1
小时表结果如下:2012-08-17 01:00:00.000 3.1
2012-08-17 02:00:00.000 4.8
2012-08-17 03:00:00.000 4.6
2012-08-17 04:00:00.000 21.6
FROM [tb]
GROUP BY DATEADD(HOUR,1,CONVERT(VARCHAR(13),[日期时间],120)+':00:00.000')
DATEADD(HOUR,1,CONVERT(VARCHAR(13),[日期时间],120)+':00:00.000') END ,SUM([10分钟雨量])
FROM [tb]
GROUP BY CASE WHEN CONVERT(VARCHAR(13),[日期时间],120)+':00:00.000'= [日期时间] THEN [日期时间] ELSE
DATEADD(HOUR,1,CONVERT(VARCHAR(13),[日期时间],120)+':00:00.000') END
筱筱澄正解!我还准备用ROW_NUMBER解决问题的,也发现有整点不好搞。shit
go
create table test(dt datetime,yl float)
go
insert into test
select '2012-08-17 00:10:00.000', 1.3 union all
select '2012-08-17 00:20:00.000', .0 union all
select '2012-08-17 00:30:00.000', .0 union all
select '2012-08-17 00:40:00.000', .0 union all
select '2012-08-17 00:50:00.000', 1.8 union all
select '2012-08-17 01:00:00.000', .0 union all
select '2012-08-17 01:10:00.000', .0 union all
select '2012-08-17 01:20:00.000', .0 union all
select '2012-08-17 01:30:00.000', .4 union all
select '2012-08-17 01:40:00.000', .0 union all
select '2012-08-17 01:50:00.000', 4.4 union all
select '2012-08-17 02:00:00.000', .0 union all
select '2012-08-17 02:10:00.000', 2.2 union all
select '2012-08-17 02:20:00.000', 1.6 union all
select '2012-08-17 02:30:00.000', .0 union all
select '2012-08-17 02:40:00.000', .0 union all
select '2012-08-17 02:50:00.000', .0 union all
select '2012-08-17 03:00:00.000', .8 union all
select '2012-08-17 03:10:00.000', 1.7 union all
select '2012-08-17 03:20:00.000', 1.3 union all
select '2012-08-17 03:30:00.000', 2.0 union all
select '2012-08-17 03:40:00.000', 2.6 union all
select '2012-08-17 03:50:00.000', 4.3 union all
select '2012-08-17 04:00:00.000', 9.7
go
;with t as
(
select convert(varchar(10),dt,120) dt
,convert(varchar(2),case when datepart(minute,dt)=0 and datepart(ss,dt)=0 then datepart(hh,dt) else datepart(hh,dt)+1 end) hr
,sum(yl) yl from test
group by convert(varchar(10),dt,120),(case when datepart(minute,dt)=0 and datepart(ss,dt)=0 then datepart(hh,dt) else datepart(hh,dt)+1 end)
)
select dt+' '+(case when len(hr)<2 then '0'+hr else hr end)+':00:000' dt,yl from t/*
dt yl
--------------------- ----------------------
2012-08-17 01:00:000 3.1
2012-08-17 02:00:000 4.8
2012-08-17 03:00:000 4.6
2012-08-17 04:00:000 21.6
*/