原始问题
我不知道我是否应该提出一个新问题或取消标记最后一个问题!
所以,我想做的是:
WorkWeek Catg Item Cost
WorkWeek1 Cat1 Item1 Price
WorkWeek1 Cat1 Item2 Price
WorkWeek1 Cat2 Item3 Price
WorkWeek1 Cat3 Item4 Price
WorkWeek1 Cat3 Item1 Price
WorkWeek2 Cat1 Item1 Price
WorkWeek2 Cat2 Item2 Price
WorkWeek3 Cat1 Item1 Price
WorkWeek4 Cat1 Item2 Price
.
.
WorkWeekA CatB ItemC Price
我想创建一个新表,列出每个工作周,然后列出该工作周中每个类别的价格总计。这就是我现在正在做的事情,但是查询只是将每个 ww 的所有内容相加,为所有工作周提供相同的总和:
select
workweek
,(select sum(cost) from DataTable where Catg = 'Cat1') as Cat1TotalCost
,(select sum(cost) from DataTable where Catg = 'Cat2') as Cat2TotalCost
,(select sum(cost) from DataTable where Catg = 'Cat3') as Cat3TotalCost
.
.
.
.
from DataTable
group by Workweek
您还可以像这样进行枢轴操作:
select workweek,
sum(case when Catg = 'Cat1' then cost end) as Cat1TotalCost,
sum(case when Catg = 'Cat2' then cost end) as Cat2TotalCost,
sum(case when Catg = 'Cat3' then cost end) as Cat3TotalCost
from DataTable
group by Workweek
您不应该为每个值执行单独的子查询。
The pivot
声明也是一个非常合理的选择。我倾向于坚持使用显式版本(上面),因为它让我在添加列时更加灵活。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)