站号 日期 时间 小时雨量
..........
M1051 20051219 0000 0
M1051 20051219 0100 0
M1051 20051219 0200 0
M1051 20051219 0600 1
M1051 20051219 0700 0
M1051 20051219 0800 0
M1051 20051219 0900 10
M1051 20051219 1000 5
M1051 20051219 1100 0
M1051 20051219 1200 0
M1051 20051219 1700 0
M1051 20051219 1800 0
M1051 20051219 2000 0
M1051 20051219 2200 8
M1051 20051219 2300 0
M1051 20051220 0000 0
M1051 20051220 0100 2
M1051 20051220 0200 0
M1051 20051220 0600 0
M1051 20051220 0700 0
M1051 20051220 0800 5
M1051 20051220 0900 0
M1051 20051220 1000 0
M1051 20051220 1100 0
M1051 20051220 1200 0
M1051 20051220 1300 0
M1051 20051220 1600 0
M1051 20051220 1700 0
M1051 20051220 1900 1
M1051 20051220 2000 1
M1051 20051220 2100 0
M1051 20051220 2200 1
M1051 20051220 2300 0
M1051 20051221 0000 0
M1051 20051221 0100 0
M1051 20051221 0200 0
M1051 20051221 0300 0
M1051 20051221 0400 0
M1051 20051221 0500 3
M1051 20051221 0800 0
..............
我现在想统计每天09时-第二天08时的累计雨量
如以上表,结果为站号 日期 雨量
M1051 20051219 1 //(2005年12月18日0900-19日0800时累计)
M1051 20051220 25 //(19日0900-20日0800时累计)
M1051 20051221 6 //(20日0900-21日0800时累计)
......
..........
M1051 20051219 0000 0
M1051 20051219 0100 0
M1051 20051219 0200 0
M1051 20051219 0600 1
M1051 20051219 0700 0
M1051 20051219 0800 0
M1051 20051219 0900 10
M1051 20051219 1000 5
M1051 20051219 1100 0
M1051 20051219 1200 0
M1051 20051219 1700 0
M1051 20051219 1800 0
M1051 20051219 2000 0
M1051 20051219 2200 8
M1051 20051219 2300 0
M1051 20051220 0000 0
M1051 20051220 0100 2
M1051 20051220 0200 0
M1051 20051220 0600 0
M1051 20051220 0700 0
M1051 20051220 0800 5
M1051 20051220 0900 0
M1051 20051220 1000 0
M1051 20051220 1100 0
M1051 20051220 1200 0
M1051 20051220 1300 0
M1051 20051220 1600 0
M1051 20051220 1700 0
M1051 20051220 1900 1
M1051 20051220 2000 1
M1051 20051220 2100 0
M1051 20051220 2200 1
M1051 20051220 2300 0
M1051 20051221 0000 0
M1051 20051221 0100 0
M1051 20051221 0200 0
M1051 20051221 0300 0
M1051 20051221 0400 0
M1051 20051221 0500 3
M1051 20051221 0800 0
..............
我现在想统计每天09时-第二天08时的累计雨量
如以上表,结果为站号 日期 雨量
M1051 20051219 1 //(2005年12月18日0900-19日0800时累计)
M1051 20051220 25 //(19日0900-20日0800时累计)
M1051 20051221 6 //(20日0900-21日0800时累计)
......
大致思路 时间是字符型吗?可能要做转换
昨天的09点以后 + 今天的08点以前
效率可能不高 等高手
select distinct 站号,日期,(select sum(雨量)
from dbo.tb
where 站号=t.站号
and (日期=t.日期 and 时间<=0800
or 日期=t.日期-1 and 时间>=0900)
from dbo.tb as t;
select distinct 站号,日期,(select sum(雨量)
from dbo.tb
where 站号=t.站号
and (日期=t.日期 and 时间<=0800
or 日期=t.日期-1 and 时间>=0900))as 雨量
from dbo.tb as t;
create table tb(co1 nchar(10),col2 nchar(10),col3 nchar(4),col4 nchar(4))
insert into tb
select
'M1051','20051219','0000','0' union all select
'M1051','20051219','0100','0' union all select
'M1051','20051219','0200','0'union all select
'M1051','20051219','0600', '1' union all select
'M1051','20051219','0700','0' union all select
'M1051','20051219','0800','0' union all select
'M1051','20051219','0900', '10' union all select
'M1051','20051219','1000','5' union all select
'M1051','20051219','1100','0' union all select
'M1051','20051219','1200','0' union all select
'M1051','20051219','1700','0' union all select
'M1051','20051219','1800','0' union all select
'M1051','20051219','2000','0' union all select
'M1051','20051219','2200','8' union all select
'M1051','20051219','2300','0' union all select
'M1051','20051220','0000','0' union all select
'M1051','20051220','0100','2' union all select
'M1051','20051220','0200','0' union all select
'M1051','20051220','0600','0' union all select
'M1051','20051220','0700','0' union all select
'M1051','20051220','0800', '5' union all select
'M1051','20051220','0900','0' union all select
'M1051','20051220','1000','0' union all select
'M1051','20051220','1100','0' union all select
'M1051','20051220','1200','0' union all select
'M1051','20051220','1300','0' union all select
'M1051','20051220','1600','0' union all select
'M1051','20051220','1700','0' union all select
'M1051','20051220','1900', '1' union all select
'M1051','20051220','2000', '1' union all select
'M1051','20051220','2100','0' union all select
'M1051','20051220','2200', '1' union all select
'M1051','20051220','2300','0' union all select
'M1051','20051221','0000','0' union all select
'M1051','20051221','0100','0' union all select
'M1051','20051221','0200','0' union all select
'M1051','20051221','0300','0' union all select
'M1051','20051221','0400','0'union all select
'M1051','20051221','0500', '3'union all select
'M1051','20051221','0800','0'select 站点=co1,日期=case when cast(left(col3,2) as int)>=9 then dateadd(day,0,cast(col2 as datetime )) else dateadd(day,-1,cast(col2 as datetime )) end
,雨量=sum(cast(col4 as numeric(10,2)) )
from tb group by co1 ,
case when cast(left(col3,2) as int)>=9 then dateadd(day,0,cast(col2 as datetime )) else dateadd(day,-1,cast(col2 as datetime )) enddrop table tb
/*
(所影响的行数为 40 行)站点 日期 雨量
---------- ------------------------------------------------------ ----------------------------------------
M1051 2005-12-18 00:00:00.000 1.00
M1051 2005-12-19 00:00:00.000 30.00
M1051 2005-12-20 00:00:00.000 6.00(所影响的行数为 3 行)
*/
from tb
group by 站号 , convert(varchar(10),dateadd(hh,-9,cast(日期 + ' ' + left(时间,2) + ':' + right(时间,2) + ':00' as datetime)),120)
insert into tb
select
'M1051','20051219','0000',0 union all select
'M1051','20051219','0100',0 union all select
'M1051','20051219','0200',0 union all select
'M1051','20051219','0600',1 union all select
'M1051','20051219','0700',0 union all select
'M1051','20051219','0800',0 union all select
'M1051','20051219','0900',10 union all select
'M1051','20051219','1000',5 union all select
'M1051','20051219','1100',0 union all select
'M1051','20051219','1200',0 union all select
'M1051','20051219','1700',0 union all select
'M1051','20051219','1800',0 union all select
'M1051','20051219','2000',0 union all select
'M1051','20051219','2200',8 union all select
'M1051','20051219','2300',0 union all select
'M1051','20051220','0000',0 union all select
'M1051','20051220','0100',2 union all select
'M1051','20051220','0200',0 union all select
'M1051','20051220','0600',0 union all select
'M1051','20051220','0700',0 union all select
'M1051','20051220','0800',5 union all select
'M1051','20051220','0900',0 union all select
'M1051','20051220','1000',0 union all select
'M1051','20051220','1100',0 union all select
'M1051','20051220','1200',0 union all select
'M1051','20051220','1300',0 union all select
'M1051','20051220','1600',0 union all select
'M1051','20051220','1700',0 union all select
'M1051','20051220','1900',1 union all select
'M1051','20051220','2000',1 union all select
'M1051','20051220','2100',0 union all select
'M1051','20051220','2200',1 union all select
'M1051','20051220','2300',0 union all select
'M1051','20051221','0000',0 union all select
'M1051','20051221','0100',0 union all select
'M1051','20051221','0200',0 union all select
'M1051','20051221','0300',0 union all select
'M1051','20051221','0400',0 union all select
'M1051','20051221','0500',3 union all select
'M1051','20051221','0800',0select 站号 , convert(varchar(10),dateadd(hh,-9,cast(日期 + ' ' + left(时间,2) + ':' + right(时间,2) + ':00' as datetime)),120) 日期,sum(小时雨量) 雨量
from tb
group by 站号 , convert(varchar(10),dateadd(hh,-9,cast(日期 + ' ' + left(时间,2) + ':' + right(时间,2) + ':00' as datetime)),120)drop table tb/*
站号 日期 雨量
---------- ---------- -----------
M1051 2005-12-18 1
M1051 2005-12-19 30
M1051 2005-12-20 6(所影响的行数为 3 行)
*/
if object_id('tempdb.dbo.#') is not null drop table #
create table #(sno varchar(8), date varchar(8), time varchar(4), rain int)
insert into #
select 'M1051', '20051219', '0000', 0 union all
select 'M1051', '20051219', '0100', 0 union all
select 'M1051', '20051219', '0200', 0 union all
select 'M1051', '20051219', '0600', 1 union all
select 'M1051', '20051219', '0700', 0 union all
select 'M1051', '20051219', '0800', 0 union all
select 'M1051', '20051219', '0900', 10 union all
select 'M1051', '20051219', '1000', 5 union all
select 'M1051', '20051219', '1100', 0 union all
select 'M1051', '20051219', '1200', 0 union all
select 'M1051', '20051219', '1700', 0 union all
select 'M1051', '20051219', '1800', 0 union all
select 'M1051', '20051219', '2000', 0 union all
select 'M1051', '20051219', '2200', 8 union all
select 'M1051', '20051219', '2300', 0 union all
select 'M1051', '20051220', '0000', 0 union all
select 'M1051', '20051220', '0100', 2 union all
select 'M1051', '20051220', '0200', 0 union all
select 'M1051', '20051220', '0600', 0 union all
select 'M1051', '20051220', '0700', 0 union all
select 'M1051', '20051220', '0800', 5 union all
select 'M1051', '20051220', '0900', 0 union all
select 'M1051', '20051220', '1000', 0 union all
select 'M1051', '20051220', '1100', 0 union all
select 'M1051', '20051220', '1200', 0 union all
select 'M1051', '20051220', '1300', 0 union all
select 'M1051', '20051220', '1600', 0 union all
select 'M1051', '20051220', '1700', 0 union all
select 'M1051', '20051220', '1900', 1 union all
select 'M1051', '20051220', '2000', 1 union all
select 'M1051', '20051220', '2100', 0 union all
select 'M1051', '20051220', '2200', 1 union all
select 'M1051', '20051220', '2300', 0 union all
select 'M1051', '20051221', '0000', 0 union all
select 'M1051', '20051221', '0100', 0 union all
select 'M1051', '20051221', '0200', 0 union all
select 'M1051', '20051221', '0300', 0 union all
select 'M1051', '20051221', '0400', 0 union all
select 'M1051', '20051221', '0500', 3 union all
select 'M1051', '20051221', '0800', 0select sno, convert(varchar(10),dateadd(hour,15+time/100, ltrim(date)),112)date, sum(rain)rain from #
group by sno, convert(varchar(10),dateadd(hour,15+time/100, ltrim(date)),112)/*
sno date rain
-------- ---------- -----------
M1051 20051219 1
M1051 20051220 30
M1051 20051221 6
*/