mysql - 选择 * 以及另一个表中的计数/总和

Question

我正在尝试编写一个MySQL查询来选择表a中的所有行以及表b中的信息，同时还查询另一个表c中a的每一行的值的计数和总和。

我会尝试更好地分解它，这是我的表格的简化版本:

Table A

+---------+----------+-----------+
| id      | name     |      bid  |
+---------+----------+-----------+
| 1       | abc      |         1 |
| 2       | def      |         1 |
| 3       | ghi      |         2 |
+--------------------------------+

Table B

+---------+----------+
| id      | name     |
+---------+----------+
| 1       | STAN     |      
| 2       | UCLA     | 
+--------------------+

Table C

+---------+----------+-----------+
| id      | aid      |   cnumber |
+---------+----------+-----------+
| 1       | 1        |        40 |
| 2       | 1        |        20 |
| 3       | 2        |        10 |
| 4       | 3        |        40 |
| 5       | 3        |        20 |
| 6       | 3        |        10 |
+--------------------------------+

我需要的是一个将返回包含

的行的查询

a.id | a.name | b.id | b.name | SUM(c.cnumber) | COUNT(c.cnumber)

我不确定这样的事情在 MySQL 中是否可能。

我当前的解决方案是尝试查询 A + B 左联接，然后与 A + C 右联接进行 UNION。然而它并没有给我我想要的结果。

谢谢。

编辑:针对此问题重写的当前查询:

SELECT
 a.id,
 a.name,
 b.id,
 b.name
 "somecolumn" as dummy_column
 "somecolumn1" as dummy_column1
FROM a
LEFT OUTER JOIN b ON a.b.id = b.id
UNION
SELECT
 "somecolumn" as dummy_column
 "somecolumn1" as dummy_column1
 "somecolumn2" as dummy_column2
 "somecolumn3" as dummy_column3
COUNT(c.cnumber) AS ccount
SUM(c.cnumber) AS sum
FROM a
RIGHT OUTER JOIN c ON a.id = c.a.id; 

不幸的是，MySQL 没有 FULL OUTER JOIN，这是我的临时解决方案，尽管我认为这不是正确的想法。

我想要的输出是表 A 中的所有行以及表 B 中的一些信息，以及表 C 中的总输入。

编辑2:

        SELECT
          a.id,
          a.name,
          b.id,
          SUM(c.cnumber) as totalSum,
          (SELECT count(*) FROM c as cc WHERE cc.aid = a.id) as totalCount
        FROM
          a
        LEFT JOIN b ON a.bid = b.id
        LEFT JOIN c ON c.aid = a.id;

对于以后类似的问题，解决方案:

        SELECT
          a.id AS aid,
          a.name,
          b.id,
          (SELECT SUM(c.rating) FROM c WHERE c.aid = aid) AS totalSum,
          (SELECT COUNT(c.rating) FROM c WHERE c.aid = aid) AS totalCount
        FROM
          a
        LEFT JOIN b ON a.bid = b.id;

Answer 1

您的查询应该是:-

SELECT
      a.id,
      a.name,
      b.id,
      (SELECT SUM(c.cnumber) FROM c as cd WHERE cd.aid = a.id) as totalSum,
      (SELECT count(*) FROM c as cc WHERE cc.aid = a.id) as totalCount
    FROM
      a
    LEFT JOIN b ON a.bid = b.id
    LEFT JOIN c ON c.aid = a.id;

它可能对你有帮助。