好啊
所以你想得到按大小分组的所有口味的总价格,
总价格是根据每种口味的价格+馅料的价格+冰块的价格计算的,对吗?
选项1。
如果尺寸是静态的并且从不改变,
你可以这样做
select Flavor, SUM(aPrice) as aPrice, SUM(bPrice) as bPrice, SUM(cPrice) as cPrice, SUM(dPrice) as dPrice
from (
select c.Name_en as Flavor,
ISNULL(b.Price, 0) + ISNULL(c.Price, 0) + ISNULL(d.Price, 0) + ISNULL(f.Price, 0) as aPrice,
0 as bPrice, 0 as cPrice, 0 as dPrice
from cake a
Left JOIN CakeSize b ON a.SizeId = b.SizeId
Left JOIN CakeFlavor c ON a.FlavorId = c.FlavorId
Left JOIN CakeFilling d ON a.FillingId = d.FillingId
Left JOIN CakeIcing f ON a.IcingId = f.IcingId
where b.sizeid = '1'
UNION ALL
select c.Name_en as Flavor, 0 as aPrice,
ISNULL(b.Price, 0) + ISNULL(c.Price, 0) + ISNULL(d.Price, 0) + ISNULL(f.Price, 0) as bPrice,
0 as cPrice, 0 as dPrice
from cake a
Left JOIN CakeSize b ON a.SizeId = b.SizeId
Left JOIN CakeFlavor c ON a.FlavorId = c.FlavorId
Left JOIN CakeFilling d ON a.FillingId = d.FillingId
Left JOIN CakeIcing f ON a.IcingId = f.IcingId
where b.sizeid = '2'
UNION ALL
select c.Name_en as Flavor, 0 as aPrice, 0 as bPrice,
ISNULL(b.Price, 0) + ISNULL(c.Price, 0) + ISNULL(d.Price, 0) + ISNULL(f.Price, 0) as cPrice,
0 as dPrice
from cake a
Left JOIN CakeSize b ON a.SizeId = b.SizeId
Left JOIN CakeFlavor c ON a.FlavorId = c.FlavorId
Left JOIN CakeFilling d ON a.FillingId = d.FillingId
Left JOIN CakeIcing f ON a.IcingId = f.IcingId
where b.sizeid = '3'
UNION ALL
select c.Name_en as Flavor, 0 as aPrice, 0 as bPrice, 0 as cPrice,
ISNULL(b.Price, 0) + ISNULL(c.Price, 0) + ISNULL(d.Price, 0) + ISNULL(f.Price, 0) as dPrice
from cake a
Left JOIN CakeSize b ON a.SizeId = b.SizeId
Left JOIN CakeFlavor c ON a.FlavorId = c.FlavorId
Left JOIN CakeFilling d ON a.FillingId = d.FillingId
Left JOIN CakeIcing f ON a.IcingId = f.IcingId
where b.sizeid = '4'
) a
where flavor is not null
group by Flavor
选项2。
如果大小是动态的,可以在列和行之间转换。。。
你可以在这里学习:
Simple way to transpose columns and rows in Sql?
编辑:在第三和第四选择之间添加“UNION ALL”,感谢Sam Axe
编辑:在第二个查询中添加逗号
编辑:添加“风味不为空”以避免风味为空