22,209
社区成员
发帖
与我相关
我的任务
分享
;with tableA
as
(
你的子查询语句
)
select *,[sum]=(select sum(VALUE) from tableA b where b.tv>=a.tv and b.tv<=dateadd(day,2,a.tv))
from tableA a
where tv<='2013-12-08'
2、就是临时表,不过这个就是多个语句了,不是一个语句,改了一下:
if OBJECT_ID('tempdb..#tableA') is not null
drop table #tableA
select * into #tableA
from 你的子查询语句
select *,[sum]=(select sum(VALUE) from #tableA b where b.tv>=a.tv and b.tv<=dateadd(day,2,a.tv))
from #tableA a
where tv<='2013-12-08'
;with tableA
as
(
你的子查询语句
)
select *,[sum]=(select sum(VALUE) from tableA b where b.tv>=a.tv and b.tv<=dateadd(day,2,a.tv))
from tableA a
where tv<='2013-12-08'
2、就是临时表,不过这个就是多个语句了,不是一个语句:
if OBJECT_ID('tempdb..#tableA') is not null
drop table #tableA
select * into #tableA
from 你的子查询语句
select *,[sum]=(select sum(VALUE) from tableA b where b.tv>=a.tv and b.tv<=dateadd(day,2,a.tv))
from #tableA a
where tv<='2013-12-08'
create table tableA
(code varchar(20),tv datetime,value varchar(20))
insert into tableA
select '2000','2013-12-05 00:00:00.000','0.5' union all
select '2000','2013-12-06 00:00:00.000','1.5' union all
select '2000','2013-12-07 00:00:00.000','2.3' union all
select '2000','2013-12-08 00:00:00.000','0.4' union all
select '2000','2013-12-09 00:00:00.000','4.1' union all
select '2000','2013-12-10 00:00:00.000','0.1'
with t as
(select code,tv,value,
row_number() over(partition by code order by tv) 'rn'
from tableA)
select a.code,a.tv,a.value,
cast(a.value as decimal(5,1))+
cast(b.value as decimal(5,1))+
cast(c.value as decimal(5,1)) 'SUM'
from t a
left join t b on a.rn=b.rn-1
left join t c on a.rn=c.rn-2
where b.rn is not null and c.rn is not null
/*
code tv value SUM
-------------------- ----------------------- -------------------- --------
2000 2013-12-05 00:00:00.000 0.5 4.3
2000 2013-12-06 00:00:00.000 1.5 4.2
2000 2013-12-07 00:00:00.000 2.3 6.8
2000 2013-12-08 00:00:00.000 0.4 4.6
(4 row(s) affected)
*/
select code,
tv,
value,
(SELECT sum(cast(isnull(VALUE, 0) as decimal(10, 3)))
FROM A as I
WHERE DATEDIFF(day, O.tv, I.tv) >= 0
and DATEDIFF(day, O.tv, I.tv) <= 2) as sumvalue
from A as O
create table tableA(code varchar(20),
tv datetime,
value numeric(10,1)
)
insert into tableA
select 2000,'2013-12-05 00:00:00.000',0.5
union all select 2000,'2013-12-06 00:00:00.000',1.5
union all select 2000,'2013-12-07 00:00:00.000',2.3
union all select 2000,'2013-12-08 00:00:00.000',0.4
union all select 2000,'2013-12-09 00:00:00.000',4.1
union all select 2000,'2013-12-10 00:00:00.000',0.1
go
select *,[sum]=(select sum(VALUE) from tableA b where b.tv>=a.tv and b.tv<=dateadd(day,2,a.tv))
from tableA a
where tv<='2013-12-08'
drop table tableA
/*
code tv value sum
----------------------------------------------------
2000 2013-12-05 00:00:00.000 .5 4.3
2000 2013-12-06 00:00:00.000 1.5 4.2
2000 2013-12-07 00:00:00.000 2.3 6.8
2000 2013-12-08 00:00:00.000 .4 4.6
*/