SQL SERVER-按分区求和

debugcn 发表于 Dev

CM2K

 rN         rD          rnc      d     expectedResult
abc1m      2010-03-31   abc     5.7    5.7 + 1.7 +9.6
abc3m      2010-03-31   abc     5.7    5.7 + 1.7 +9.6
abc1y      2010-03-31   abc     5.7    5.7 + 1.7 +9.6
xfx1m      2010-03-31   xfx     1.7    5.7 + 1.7 +9.6
xfx3m      2010-03-31   xfx     1.7    5.7 + 1.7 +9.6
xfx1y      2010-03-31   xfx     1.7    5.7 + 1.7 +9.6
tnt1m      2010-03-31   tnt     9.6    5.7 + 1.7 +9.6
tnt3m      2010-03-31   tnt     9.6    5.7 + 1.7 +9.6
tnt1y      2010-03-31   tnt     9.6    5.7 + 1.7 +9.6
------------------------------------
abc1m      2010-04-01   abc     2.2    2.2 + 8.9 + 5.5
abc3m      2010-04-01   abc     2.2    2.2 + 8.9 + 5.5
abc1y      2010-04-01   abc     2.2    2.2 + 8.9 + 5.5
xfx1m      2010-04-01   xfx     8.9    2.2 + 8.9 + 5.5
xfx3m      2010-04-01   xfx     8.9    2.2 + 8.9 + 5.5
xfx1y      2010-04-01   xfx     8.9    2.2 + 8.9 + 5.5
tnt1m      2010-04-01   tnt     5.5    2.2 + 8.9 + 5.5
tnt3m      2010-04-01   tnt     5.5    2.2 + 8.9 + 5.5
tnt1y      2010-04-01   tnt     5.5    2.2 + 8.9 + 5.5

预期结果是特定日期rnc的总和。如何实现此目标。我想使用类似下面的代码，但是不起作用。

select *,
sum (d) over (partition by rD, distinct rnc) as expectedResult
from myTable
where ...--some condition
order by ...--order by some columns

使用SQL Server 2012，谢谢

编辑：关于搁置的问题，这还不清楚。如果只看一眼，expectedResult不是很清楚吗？我应该添加些什么才能使其变得更好？-每个rnc都有d。只需假设每个集合都具有示例中给出的形式即可。（回答一条评论）

Veljko89

由于第一列的最后2个字符是可重复的，因此您实际上是在分区中求和，试一下，让我知道这是否是您要的

create table #TempTable (rn nvarchar(10), rD date, rnc nvarchar(10), d decimal(5,2))

insert into #TempTable (rn, rD, rnc, d)
values
('abc1m','2010-03-31','abc',     5.7),
('abc3m','2010-03-31','abc',     5.7),
('abc1y','2010-03-31','abc',     5.7),
('xfx1m','2010-03-31','xfx',     1.7),
('xfx3m','2010-03-31','xfx',     1.7),
('xfx1y','2010-03-31','xfx',     1.7),
('tnt1m','2010-03-31','tnt',     9.6),
('tnt3m','2010-03-31','tnt',     9.6),
('tnt1y','2010-03-31','tnt',     9.6),
------------------------------------
('abc1m','2010-04-01','abc',     2.2),
('abc3m','2010-04-01','abc',     2.2),
('abc1y','2010-04-01','abc',     2.2),
('xfx1m','2010-04-01','xfx',     8.9),
('xfx3m','2010-04-01','xfx',     8.9),
('xfx1y','2010-04-01','xfx',     8.9),
('tnt1m','2010-04-01','tnt',     5.5),
('tnt3m','2010-04-01','tnt',     5.5),
('tnt1y','2010-04-01','tnt',     5.5)

select rn, rD, rnc, d, SUM(d) over (partition by right(rn,2), rD) as 'Sum' 
from #TempTable
order by Rd

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。