MySQL 2个不同表中的两个值之和 [英] mySQL sum of two values in 2 different tables

问题描述

我有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
...

我需要加入表格并输出数量字段的总和对于每个匹配的参考ID，例如：

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

reference    total_quantity
TS00001      905
TS00002      610
TS00003      1000
...

我一直在尝试LEFT JOIN和其他方法，但我我无法快速到达任何地方，因此，如果有人可以抽出时间将我带入正确的轨道，我将非常感激。谢谢。

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.

推荐答案

您需要 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

这样，即使包含在 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.

这篇关于MySQL 2个不同表中的两个值之和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！