SQL聚合查询，按联结表中的条目分组

发布于 2024-12-17 10:04:09 字数 493 浏览 4 评论 0原文

我通过 TableB 将 TableA 与 TableC 建立多对多关系。也就是说，

TableA       TableB           TableC
id | val     fkeyA | fkeyC    id | data

我希望在 TableA 上执行 select sum(val)，并按与 TableC 的关系进行分组。 TableA 中的每个条目与TableC 至少有一个关系。例如，

TableA
1 | 25
2 | 30
3 | 50

TableB
1 | 1
1 | 2
2 | 1
2 | 2
2 | 3
3 | 1
3 | 2

应该输出

75
30

，因为表中的行 1 和行 3 与表 C 具有相同的关系，但表 A 中的行 2 与表 C 具有不同的关系。

我该如何为此编写 SQL 查询？

原文

I have TableA in a many-to-many relationship with TableC via TableB. That is,

TableA       TableB           TableC
id | val     fkeyA | fkeyC    id | data

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

should output

75
30

since rows 1 and 3 in Table have the same relationships to TableC, but row 2 in TableA has a different relationship to TableC.

How can I write a SQL query for this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

旧情别恋 2024-12-24 10:04:09

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

编辑
啊哈 - 我现在明白你的意思了。让我再试一次：

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

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

回复收藏 0 原文

瑾夏年华 2024-12-24 10:04:09

嗯，在 MySQL 中可以这样写：

SELECT
    SUM(val) AS sumVal
FROM
    ( SELECT
          fkeyA
        , GROUP_CONCAT(fkeyC ORDER BY fkeyC) AS grpC
      FROM 
          TableB
      GROUP BY
          fkeyA
    ) AS g
  JOIN
    TableA a
      ON a.id = g.fkeyA
GROUP BY 
    grpC

Hm, in MySQL it could be written like this:

SELECT
    SUM(val) AS sumVal
FROM
    ( SELECT
          fkeyA
        , GROUP_CONCAT(fkeyC ORDER BY fkeyC) AS grpC
      FROM 
          TableB
      GROUP BY
          fkeyA
    ) AS g
  JOIN
    TableA a
      ON a.id = g.fkeyA
GROUP BY 
    grpC

回复收藏 0 原文

胡大本事 2024-12-24 10:04:09

SELECT sum(a.val) 
FROM  tablea a
INNER JOIN tableb b ON (b.fKeyA = a.id)
GROUP BY b.fKeyC

SELECT sum(a.val) 
FROM  tablea a
INNER JOIN tableb b ON (b.fKeyA = a.id)
GROUP BY b.fKeyC

回复收藏 0 原文

自由如风 2024-12-24 10:04:09

似乎需要创建一个 key_list 才能允许分组依据：

75 -> key list = "1 2"
30 -> key list = "1 2 3"

因为 T-SQL 中不存在 GROUP_CONCAT：

WITH CTE ( Id, key_list )
          AS ( SELECT TableA.id, CAST( '' AS VARCHAR(8000) )
                 FROM TableA 
                GROUP BY TableA.id
                UNION ALL
               SELECT TableA.id, CAST( key_list + ' ' + str(TableB.id) AS VARCHAR(8000) )
                 FROM CTE c
                INNER JOIN TableA A
                   ON c.Id = A.id
                INNER join TableB B
                   ON B.Id = A.id
                WHERE A.id > c.id      --avoid infinite loop
              )
Select
   sum( val )
from 
   TableA inner join
   CTE on (tableA.id = CTE.id)
group by
   CTE.key_list

It seems that is it needed to create a key_list in orther to allow group by:

75 -> key list = "1 2"
30 -> key list = "1 2 3"

Because GROUP_CONCAT don't exists in T-SQL:

WITH CTE ( Id, key_list )
          AS ( SELECT TableA.id, CAST( '' AS VARCHAR(8000) )
                 FROM TableA 
                GROUP BY TableA.id
                UNION ALL
               SELECT TableA.id, CAST( key_list + ' ' + str(TableB.id) AS VARCHAR(8000) )
                 FROM CTE c
                INNER JOIN TableA A
                   ON c.Id = A.id
                INNER join TableB B
                   ON B.Id = A.id
                WHERE A.id > c.id      --avoid infinite loop
              )
Select
   sum( val )
from 
   TableA inner join
   CTE on (tableA.id = CTE.id)
group by
   CTE.key_list

回复收藏 0 原文

~没有更多了~