首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >MySQL连接两个表,每个表中都有group by

问MySQL连接两个表,每个表中都有group by
EN

Stack Overflow用户
提问于 2018-06-08 13:37:37
回答 2查看 267关注 0票数 0

我有两个带有零件号和qty的mysql表,我想把每个表的qty sum(qty) ... group by partNumber求和,然后在零件号上连接这两个表。

有时表A会有表B没有的零件号,反之亦然。下面是我所期待的图像。

我尝试过这样的方法,但它为每个表返回一行,并且我希望它返回1个组合行

代码语言:javascript
复制
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

代码语言:javascript
复制
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

​

​

EN

回答 2

Stack Overflow用户

发布于 2018-06-08 14:18:54

一种方法是在两个表中识别唯一的零件号(使用UNION并应用distinct),然后使用相关子查询来获得总和。例如

代码语言:javascript
复制
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)
票数 1
EN

Stack Overflow用户

发布于 2018-06-08 13:43:41

我将其表述为两个子查询之间的连接,每个子查询在各自的表中查找总和。但是,由于每个表不一定包含所有零件号,并且实际上每个表可能有唯一的零件号,因此我们将不得不使用完全外连接方法。

代码语言:javascript
复制
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是主键的情况下创建一个部件表。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/50753989

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档