mysql通过groupby连接两个表

whlutmcx 于 2021-06-23 发布在 Mysql

关注(0)|答案(2)|浏览(474)

我有两个mysql表与零件号和数量的。我想把每张table的数量加起来 sum(qty) ... group by partNumber 然后在零件号上连接两个表。
有时表a会有表b没有的零件号，反之亦然。下面是我所期待的图片。
我尝试过这样的方法，但是它为每个表返回一行，我希望它返回一个组合行

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

我还尝试将其 Package 在一个外部选择中，并按外部选择的零件号进行分组，如下所示，但这会导致第二个内部选择始终为空

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

来源：https://stackoverflow.com/questions/50753989/mysql-join-two-tables-with-group-by-in-each

2条答案

按热度按时间

jogvjijk1#

我将把它表述为两个子查询之间的联接，每个子查询在各自的表中查找和。但是，由于每个表不一定包含所有零件号，而且事实上每个表可能都有唯一的零件号，因此我们必须使用完全外部联接方法。

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;

请记住，在正常情况下，在设计良好的数据库中，很少会遇到需要使用完全外部联接的情况。事实上，一个完整的外部连接尖叫出有一个设计问题。在本例中，没有包含所有零件号的单个零件表。该表应该存在，所以除非您喜欢大而难看的查询，否则您应该创建一个parts表，其中 partNumber 是主键。

赞(0）回复(0）举报 2021-06-23

whhtz7ly2#

一种方法是在两个表中标识唯一的零件号（使用应用了distinct的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)

赞(0）回复(0）举报 2021-06-23

我来回答

mysql通过groupby连接两个表

2条答案

相关问题

热门标签

最新问答