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聚合查询，按联结表中的条目分组

相关推荐