如何在PostgreSQL中连接两个表以求和

我的PostgreSQL数据库中有两个表。

Table1
----------------------------------------
id      group_id        amount      size
1       1                 10        L
2       2                 20        M
3       2                 80        M



Table2
-------------------------------
id      group_id        amount
1       1               10
2       2               50 
3       2               60

这些表具有一个以group_id为键的“组”表关系。我想得到一个总计表。

SumQueryResult
----------------------------------------
group_id    table_1_sum     table_2_sum
1             10            100
2             100           110

我该怎么做?

qqswddnsbakn 回答:如何在PostgreSQL中连接两个表以求和

使用子查询并加入

select a.group_id,a.amt as tableamnt,b.amt as table2amt from (
select group_id,sum(amount) as amt from table1 group by group_id
) a join (    
select group_id,sum(amount) as amt from table2 group by group_id
) b on a.group_id=b.group_id
,

您需要什么:

SELECT t1.GROUP_ID as group_id,sum(t1.AMOUNT) as table_1_sum,sum(t2.amount) as table_2_sum
from Table1 t1
left join Table2 t2
on t1.id = t2.id
group by t1.GROUP_ID
order by t1.GROUP_ID;

这里是DEMO

干杯!

这是Zaynul Abadin Tuhin答案的正确版本:

select a.group_id,sum(amount) as amt from table1 group by group_id 
) a join (    
select group_id,sum(amount) as amt from table2 group by group_id 
) b on a.group_id=b.group_id
order by group_id

这是第二个DEMO

,

您必须执行2个子查询以按group_id对数据进行分组,然后再加入2个子查询。像这样:

SELECT group_id,table_1_sum,table_2_sum from (

   SELECT
      group_id,SUM(amount) as table_1_sum
   FROM
      Table1
   GROUP BY
      group_id;

INNER JOIN

   SELECT
      group_id,SUM(amount) as table_2_sum
   FROM
      Table2
   GROUP BY
       group_id;

ON Table1.group_id = Table2.group_id
)
,

我将@Zaynul的查询修改为正确的,并且可以在Postgresql中使用。

您想通过对group_id分组来分别汇总每个表的数量。 因此,您不能直接使用JOIN查询。

您必须首先分别查询和汇总每个表。 然后加入先前查询的结果。

SQL语法将如下所示:

SELECT a.group_id,a.amt as table_1_sum,b.amt as table_2_sum FROM (
    SELECT group_id,SUM(amount) as amt FROM table1 GROUP BY group_id
) a join (    
    SELECT group_id,SUM(amount) as amt FROM table2 GROUP BY group_id
) b ON a.group_id = b.group_id

我已经在pgAdmin4中进行了测试,并获得了想要的结果。

SumQueryResult
----------------------------------------
group_id    table_1_sum     table_2_sum
1             10            10(this can't be 100)
2             100           110
本文链接:https://www.f2er.com/3168406.html

大家都在问