时间戳

首页 > 解决方案 > MySQL连接两个表，每个表都有group by

mysql - MySQL连接两个表，每个表都有group by

问题描述

我有两个带有零件号和数量的 mysql 表。我想总结每个表的数量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 * 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

在此处输入图像描述

标签： mysql

解决方案

一种方法是识别 2 个表中的唯一部件号（使用 UNION 并对其应用不同），然后使用相关子查询来获取总和。例如

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)

推荐阅读