SQL Server:在许多领域
问题描述:
我有这个表,我要计算每个TYPE_MATERIAL累计百分比累计百分比“组由” /年SQL Server:在许多领域
declare @mytable table (TYPE_MATERIAL int, YEARS int, ROW_NUM int, PERCENTUAL_PRICE numeric(6,2))
insert @mytable
select 1,2010,1,54.5
union all select 1,2010,2,37.5
union all select 1,2010,3,8.0
union all select 1,2009,1,72.8
union all select 1,2009,2,21.0
union all select 1,2009,3,6.2
union all select 2,2010,1,61.0
union all select 2,2010,2,36.0
union all select 2,2010,3,3.0
结果应该是这样的:
TYPE_MATERIAL YEARS ROW_NUM PERCENTUAL_PRICE PERCENTUAL_PRICE_cumulative
1 2010 1 54.50 54.5 (=54.5)
1 2010 2 37.50 92.0 (=54.5+37.5)
1 2010 3 8.00 100.0 (=54.5+37.5+8)
1 2009 1 72.80 72.8
1 2009 2 21.00 93.8
1 2009 3 6.20 100.0
2 2010 1 61.00 61.0
2 2010 2 36.00 97.0
2 2010 3 3.00 100.0
我在互联网上找到此查询,但由于其计算累积百分比只有当我有一个TYPE_MATERIAL和一年
这不利于这种情况下,有什么建议吗? 感谢
答
select TYPE_MATERIAL, YEARS, ROW_NUM, PERCENTUAL_PRICE,
PERCENTUAL_PRICE_cumulative=
(
select SUM(PERCENTUAL_PRICE) from @mytable b
where b.ROW_NUM<=a.ROW_NUM AND b.YEARS = a.YEARS AND b.TYPE_MATERIAL = a.TYPE_MATERIAL
)
from @mytable a
答
你的问题是由内部查询不限制在type_material和多年造成的。
select TYPE_MATERIAL, YEARS, ROW_NUM, PERCENTUAL_PRICE,
PERCENTUAL_PRICE_cumulative=
(
select SUM(PERCENTUAL_PRICE) from @mytable b
where b.ROW_NUM<=a.ROW_NUM AND b.type_material = a.type_material AND b.years = a.years
)
from @mytable a
你也可以做一个递归CTE,它应该可以执行非常大的数据。
with myCte (TYPE_MATERIAL, YEARS, ROW_NUM, PERCENTUAL_PRICE, PERCENTUAL_PRICE_cumulative)
as
(
select TYPE_MATERIAL, YEARS, ROW_NUM, PERCENTUAL_PRICE, PERCENTUAL_PRICE AS PERCENTUAL_PRICE_cumulative
FROM @mytable
WHERE row_num = 1
UNION ALL
select t.TYPE_MATERIAL, t.YEARS, t.ROW_NUM, t.PERCENTUAL_PRICE, CAST(t.PERCENTUAL_PRICE + myCte.PERCENTUAL_PRICE_cumulative AS numeric(6,2)) AS PERCENTUAL_PRICE_cumulative
FROM @mytable t
INNER JOIN myCte ON myCte.type_material = t.type_material AND myCte.years = t.years AND mycte.row_num = t.row_num-1
WHERE t.row_num > 1
)
SELECT * FROM myCte
ORDER BY TYPE_MATERIAL, YEARS, ROW_NUM
它简单而完美!谢谢!! @Seph也与CTE这是一个好主意.. – Paolo