T-sql:获取列的总和 [英] T-sql :get SUM of Columns
本文介绍了T-sql:获取列的总和的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张类似于以下内容的表格:
I have a table that looks something like the following :
W1 W2 w3
Gold 10 2 3
Silver 3 1 1
但我需要一个结果:
W1 W2 w3
Gold 10 12 15
Silver 3 4 5
有什么办法可以得到这个结果吗?
Is there any way i can get that result?
我的sql查询:
SELECT
week1=[1],week2=[2],week3=[3]
FROM
(
SELECT
[week]=DATEPART(ISO_WEEK,ta.enddate),ta.id
FROM
table1 ta where ta.enddate BETWEEN '2016/01/01' AND '2016/12/31'
) src
PIVOT
(
SUM(id) FOR week IN (
[1],[2],[3])
) piv
推荐答案
在透视数据之前计算运行总数
Calculate the running total before pivoting the data
SELECT element,
week1=[1],week2=[2],week3=[3]
FROM
(
SELECT [week] = DATEPART(ISO_WEEK,ta.enddate),
price = sum(ta.price)Over(Partition by element Order by enddate),
element
FROM table1 ta
where ta.enddate BETWEEN '2016/01/01' AND '2016/12/31'
) src
PIVOT
(
SUM(price) FOR week IN ( [1],[2],[3])
) piv
对于旧版本
SELECT element,
week1=[1],week2=[2],week3=[3]
FROM
(
SELECT [week] = DATEPART(ISO_WEEK,ta.enddate),
cs.price,
element
FROM table1 ta
cross apply(select sum(price) from table1 tb
where ta.element = tb.element and ta.enddate >= tb.enddate ) cs (price)
where ta.enddate BETWEEN '2016/01/01' AND '2016/12/31'
) src
PIVOT
(
SUM(price) FOR week IN ( [1],[2],[3])
) piv
这篇关于T-sql:获取列的总和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文