我正在尝试编写一个MySQL查询,以选择表a中的所有行以及表b中的信息,同时还要查询a的每一行中另一个表c中的值的计数和总和。
我会尝试将其分解得更好一些,这是我的表的简化版本:
表A
+---------+----------+-----------+
| id | name | bid |
+---------+----------+-----------+
| 1 | abc | 1 |
| 2 | def | 1 |
| 3 | ghi | 2 |
+--------------------------------+
表B
+---------+----------+
| id | name |
+---------+----------+
| 1 | STAN |
| 2 | UCLA |
+--------------------+
表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;
您的查询应为:-
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;
它可能会帮助您。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句