如何从两张不同数据表中计算相同水果的总数量？

数据表结构与数据 #

basket_a表 #

a | fruit_a  | number_a 
---+----------+----------
 3 | Banana   |        0
 4 | Cucumber |        0
 1 | Apple    |       50
 2 | Orange   |       45

basket_b表 #

b |  fruit_b   | number_b 
---+------------+----------
 3 | Watermelon |        0
 4 | Pear       |        0
 1 | Orange     |        5
 2 | Apple      |       30

需求 #

找出两张表中相同水果的总数量，期望结果如下：

fruit   | number 
---------+--------
 Orange  |     80
 Apple   |     55

尝试的方法与遇到的问题 #

方法1：使用INNER JOIN关联 #

执行以下SQL：

select a.fruit_a, a.number_a, b.fruit_b, b.number_b 
from basket_a as a 
inner join basket_b as b on a.fruit_a=b.fruit_b;

得到结果：

fruit_a | number_a | fruit_b | number_b 
---------+----------+---------+----------
 Apple   |       50 | Apple   |       30
 Orange  |       45 | Orange  |        5

该结果仅关联出了相同水果的两条记录，但未直接计算总数量。

方法2：使用UNION合并后分组求和 #

先执行UNION合并两张表：

select * from basket_a union select * from basket_b;

得到结果：

a |  fruit_a   | number_a 
---+------------+----------
 1 | Orange     |        5
 2 | Apple      |       30
 4 | Pear       |        0
 3 | Watermelon |        0
 4 | Cucumber   |        0
 2 | Orange     |       45
 1 | Apple      |       50
 3 | Banana     |        0

尝试分组求和时执行以下SQL出现错误：

select foo.fruit_a, foo.number_a 
from (select * from basket_a union select * from basket_b) as foo 
group by foo.fruit_a;

错误信息：

ERROR:  column "foo.number_a" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select foo.fruit_a, foo.number_a from (select * from basket_...

原因是分组查询中，未被分组的列必须使用聚合函数，直接选择foo.number_a不符合SQL语法规范。

正确解决方案 #

方案1：基于INNER JOIN直接求和 #

利用INNER JOIN的关联结果，直接对两张表的数量列求和：

select a.fruit_a as fruit, a.number_a + b.number_b as number
from basket_a as a
inner join basket_b as b on a.fruit_a = b.fruit_b;

方案2：修正UNION用法后分组求和 #

先统一两张表的列名，用UNION ALL保留所有记录（避免UNION自动去重丢失数据），再分组并使用SUM()聚合函数计算总数量，最后用HAVING筛选出两张表都存在的水果：

select fruit, sum(number) as number
from (
    select fruit_a as fruit, number_a as number from basket_a
    union all
    select fruit_b as fruit, number_b as number from basket_b
) as combined
group by fruit
having count(*) > 1;

内容的提问来源于stack exchange，提问作者Ulysses Ore