postgresql 如何从两个不同的表中求出相同乘积的总和？

5jdjgkvh 于 2023-01-05 发布在 PostgreSQL

关注(0)|答案(2)|浏览(238)

有两张table-一篮子产品：网篮_a

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

网篮_b

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

我需要找到两个表中匹配的水果总量：

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

我试过内部连接
从筐_a中选择a.水果_a、a.数字_a、b.水果_b、b.数字_b作为内连接筐_b，如b关于a.水果_a=b.水果_b;

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

我试过工会选择 * 从购物篮_a并集选择 * 从购物篮_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

但我没法把它归类
根据foo.fruit_a选择foo.fruit_a、foo.number_a（选择 * 从购物篮_a并集选择 * 从购物篮_b）作为foo组;

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_...

postgresql

来源：https://stackoverflow.com/questions/74997701/how-to-find-total-sum-of-the-same-products-from-two-different-tables

2条答案

按热度按时间

mzsu5hc01#

你在哪里做这样的事情：

SELECT a.*, b.*
FROM basket_a
INNER JOIN basket_b ON basket_a.fruit_a = basketb_fruitb;

要获得2列，而不是4列，请执行以下操作：

SELECT 
    basket_a.fruit_a, 
    basket_a.number_a + basket_b.number_b as number
FROM basket_a
INNER JOIN basket_b ON basket_a.fruit_a = basketb_fruitb;

另一种不使用内部联接的方法是：

SELECT
   fruit_a,
   sum(number_a)
FROM (
      SELECT fruit_a, number_a FROM basket_a
      UNION ALL
      SELECT fruit_b, number_b FROM basket_b 
     )x
GROUP BY fruit_a;

赞(0）回复(0）举报 2023-01-05

f3temu5u2#

select fruit,sum(number) total
from
(
    select fruit_a,number
    from basket_a
    union all
    select fruit_b,number
    from basket_b
) t
group by fruit

赞(0）回复(0）举报 2023-01-05

我来回答

postgresql 如何从两个不同的表中求出相同乘积的总和？

2条答案

相关问题

热门标签

最新问答