SQL聚合查询,按联结表中的条目分组 [英] SQL aggregation query, grouping by entries in junction table
本文介绍了SQL聚合查询,按联结表中的条目分组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我通过TableB与TableC有多对多的关系.也就是说,
I have TableA in a many-to-many relationship with TableC via TableB. That is,
TableA TableB TableC
id | val fkeyA | fkeyC id | data
我希望在TableA上执行select sum(val)
,并按与TableC的关系进行分组. TableA中的每个条目都与TableC至少具有一种关系.例如,
I wish the do select sum(val)
on TableA, grouping by the relationship(s) to TableC. Every entry in TableA has at least one relationship with TableC. For example,
TableA
1 | 25
2 | 30
3 | 50
TableB
1 | 1
1 | 2
2 | 1
2 | 2
2 | 3
3 | 1
3 | 2
应输出
75
30
因为Table中的第1行和第3行与TableC具有相同的关系,但是TableA中的第2行与TableC具有不同的关系.
since rows 1 and 3 in Table have the same relationships to TableC, but row 2 in TableA has a different relationship to TableC.
如何为此编写SQL查询?
How can I write a SQL query for this?
推荐答案
SELECT
sum(tableA.val) as sumVal,
tableC.data
FROM
tableA
inner join tableB ON tableA.id = tableB.fkeyA
INNER JOIN tableC ON tableB.fkeyC = tableC.id
GROUP by tableC.data
编辑 啊哈-我现在明白你的意思了.让我再试一次:
edit Ah ha - I now see what you're getting at. Let me try again:
SELECT
sum(val) as sumVal,
tableCGroup
FROM
(
SELECT
tableA.val,
(
SELECT cast(tableB.fkeyC as varchar) + ','
FROM tableB WHERE tableB.fKeyA = tableA.id
ORDER BY tableB.fkeyC
FOR XML PATH('')
) as tableCGroup
FROM
tableA
) tmp
GROUP BY
tableCGroup
这篇关于SQL聚合查询,按联结表中的条目分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文