我有两个带有零件号和qty的mysql表,我想把每个表的qty sum(qty) ... group by partNumber求和,然后在零件号上连接这两个表。
有时表A会有表B没有的零件号,反之亦然。下面是我所期待的图像。
我尝试过这样的方法,但它为每个表返回一行,并且我希望它返回1个组合行
SELECT *, null as macroQty, sum(qty) as cardinalQty
FROM parts.cardinal where fileinfoid IN
(select cardinalFiles from parts.reports where fileinfoid = 418)
GROUP BY partNumber UNION ALL
SELECT *, sum(qty) as macroQty, null as cardinalQty
FROM parts.macro where fileinfoid IN
(select macroFiles from parts.reports where fileinfoid = 418 )
GROUP BY partNumber我还尝试将其包装在外部select中,并按外部select中的零件号进行分组,如下所示,但这会导致第二个内部select始终为null
SELECT * FROM (
SELECT *, null as macroQty, sum(qty) as cardinalQty
FROM parts.cardinal where fileinfoid IN
(select cardinalFiles from parts.reports where fileinfoid = 418)
GROUP BY partNumber UNION ALL
SELECT *, sum(qty) as macroQty, null as cardinalQty
FROM parts.macro where fileinfoid IN
(select macroFiles from parts.reports where fileinfoid = 418 )
GROUP BY partNumber
) combined GROUP BY combined.partNumber

发布于 2018-06-08 14:18:54
一种方法是在两个表中识别唯一的零件号(使用UNION并应用distinct),然后使用相关子查询来获得总和。例如
drop table if exists a,b;
create table a(id int,val int);
create table b(id int,val int);
insert into a values(1,10),(1,10),(3,10),(4,10);
insert into b values (2,10),(4,10),(4,10);
select (select sum(a.val) from a where a.id = s.id) aval,
(select sum(b.val) from b where b.id = s.id) bval,
s.id partno
from
(
select id from a
union select id from b
) s
order by s.id;
+------+------+--------+
| aval | bval | partno |
+------+------+--------+
| 20 | NULL | 1 |
| NULL | 10 | 2 |
| 10 | NULL | 3 |
| 10 | 20 | 4 |
+------+------+--------+
4 rows in set (0.00 sec)发布于 2018-06-08 13:43:41
我将其表述为两个子查询之间的连接,每个子查询在各自的表中查找总和。但是,由于每个表不一定包含所有零件号,并且实际上每个表可能有唯一的零件号,因此我们将不得不使用完全外连接方法。
SELECT
t1.partNumber,
t1.cardinalQty,
COALECSE(t2.macroQty, 0) AS macroQty
FROM
(
SELECT partNumber, SUM(qty) AS cardinalQty
FROM cardinal
GROUP BY partNumber
) t1
LEFT JOIN
(
SELECT partNumber, SUM(qty) AS macroQty
FROM macro
GROUP BY partNumber
) t2
ON t1.partNumber = t2.partNumber
UNION ALL
SELECT
t2.partNumber,
0 AS cardinalQty,
t2.macroQty
FROM
(
SELECT partNumber, SUM(qty) AS cardinalQty
FROM cardinal
GROUP BY partNumber
) t1
RIGHT JOIN
(
SELECT partNumber, SUM(qty) AS macroQty
FROM macro
GROUP BY partNumber
) t2
ON t1.partNumber = t2.partNumber
WHERE t1.partNumber IS NULL;请记住,在正常情况下,在设计良好的数据库中,很少会遇到需要使用完全外连接的情况。实际上,完整的外部连接表明存在设计问题。在这种情况下,您没有包含所有零件号的单个零件表。该表应该存在,所以除非您喜欢难看的大型查询,否则应该在partNumber是主键的情况下创建一个部件表。
https://stackoverflow.com/questions/50753989
复制相似问题