mysql 两个表的不同值,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:

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.

解决方案

You need to UNION the two tables:

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.