SQL Server 查询的多组帮助
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/943968/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Help with Multiple group by SQL Server Query
提问by Tetraneutron
I'm having some troubles to retrieve data from 2 tables with using multi "group by".
我在使用多个“分组依据”从 2 个表中检索数据时遇到了一些麻烦。
Below is 2 tables for example and the result I would like to have from my query.
例如，下面是 2 个表，以及我希望从查询中获得的结果。
PrdID Name KG
------------------
1 Hals 10
2 Hals 3
3 Kugel 4
4 Kugel 10
5 Hals 12
6 Kugel 11
7 Hals 12
8 Hals 14
9 Hals 15
10 Kugel 16
11 Hals 8
12 Hals 15
13 Kugel 7
14 Kugel 8
15 Kugel 9
Materials
材料
PrdId MatID MatSize
-------------------------
1 a 300/600
2 b 350/500
5 c 400/650
3 b 350/500
4 c 400/650
6 d 450/650
9 b 350/500
10 d 450/650
13 d 450/650
11 c 400/650
12 b 350/500
14 c 400/650
15 d 450/650
7 a 300/600
8 b 350/500
Result table
结果表
Name MatSize Kg
----------------------
Hals 300/600 22
Hals 350/500 47
Hals 400/650 20
Kugel 350/500 4
Kugel 400/650 18
Kugel 450/650 43
In summary I want to know amount of Kg used per each product on each material...
总之，我想知道每种材料上每种产品使用的公斤量......
回答by Tetraneutron
I think this is what you want
我想这就是你想要的
select P.Name, M.MatSize, sum(P.KG) as Kg
from Products P
join Material M
on P.PrdId = M.PrdId
Group By P.Name, M.MatId, M.MatSize
(Edited to return just the wnted columns)
（编辑为仅返回 wnted 列）
As a side, can I suggest you normalise your tables some more to something like Products[PrdId, Name, Kg] Materials[MatId, MatSize] ProductMaterials[PrdId, MatId]
作为一个方面，我可以建议你将你的表格标准化一些，比如 Products[PrdId, Name, Kg] Materials[MatId, MatSize] ProductMaterials[PrdId, MatId]
Then the query would become
然后查询将变成
select P.Name, M.MatSize, sum(P.KG) as KG
from Products P
join ProductMaterial PM
on PM.PrdId = P.PrdId
join Materials M
on M.MatId = PM.MatId
group by P.Name, M.MatId, P.MatSize
The benefit of this is you are only storing the MatSize once for each material type.
这样做的好处是您只为每种材料类型存储一次 MatSize。
