mySQL在2个不同表中的两个值之和

I have 2 identical tables with identical columns in each one - "quantity" and "reference". The values in these columns are set out as follows:

我有两个相同的表,每个表中有相同的列 - “数量”和“参考”。这些列中的值如下所示:

table_1
reference    quantity
TS00001      235
TS00002      400
TS00003      850
...

table_2
reference    quantity
TS00001      670
TS00002      210
TS00003      150
...

I need to join the tables and output the sum of the quantity field for each matched reference ID e.g:

我需要连接表并输出每个匹配的引用ID的数量字段的总和,例如:

reference    total_quantity
TS00001      905
TS00002      610
TS00003      1000
...

I've been trying LEFT JOIN and other methods but I'm getting nowhere quickly so if anyone could spare the time to steer me on to the right track I'd be very grateful. Thanks.

我一直在尝试LEFT JOIN和其他方法,但我很快就没有了,所以如果有人可以腾出时间引导我走上正确的轨道,我将非常感激。谢谢。

2 个解决方案

#1

You need to UNION the two tables:

你需要UNION这两个表:

SELECT reference, SUM(quantity) AS total_quantity
FROM (
  SELECT reference, quantity
  FROM table_1

  UNION ALL

  SELECT reference, quantity
  FROM table_2) AS t
GROUP BY reference

This way you are guaranteed to get a record for a reference value even if this is contained in only one of the two tables.

这样,即使只包含在两个表中的一个表中,也可以保证获得参考值的记录。

#2

You can use the union all operator to treat both columns as one:

您可以使用union all运算符将两个列视为一个:

SELECT   reference, SUM(quantity)
FROM     (SELECT reference, quantity FROM table_1
          UNION ALL
          SELECT reference, quantity FROM table_2) t
GROUP BY reference

#1