postgresql - 根据两个表计算 sal 的总和

Question

两张表一样

表1

+----+-------+-----+
| id | sname | sal |
+----+-------+-----+
|  1 | X     | 100 |
|  2 | Y     | 200 |
|  3 | Z     | 400 |
+----+-------+-----+

表2

+----+-------+-----+
| id | sname | sal |
+----+-------+-----+
|  1 | A     | 500 |
|  2 | B     | 200 |
|  3 | C     | 400
|  4   A       100
+----+-------+-----+

两个表都有关系 id 列

我需要同时根据table1.sname计算匹配到table2的sal组的总和

输出类似

+-------+-------+---------------------
| Table1.sname | Table2.sname   | sum |
+-------+-------+-----+ ----------------
| A         | W         | 600 |
| B         | Y         | 200 |
| B         | F         | 300 |
| C         | Z         | 400 |
+-------+-------+----------------------



select sum(sal),a.sname,b.sname 
from table1 a,
     (select id,sname from table2 group by sname,id) as b 
where a.id=b.id 
group by a.sname,b.sname;

但它没有给出适当的 o/p

Answer 1

你的问题有点模棱两可......但也许你想要这个。

查询

select Table11.id, Table1.sname, Table2.sname, (Table1.sal+Table2.sal) as Sum
from Table1, Table2
where Table1.id = Table2.id;

结果

 Table1.id | Table2.sname | Table2.sname | sum
-----------+--------------+--------------+-----
         1 | a            | d            | 500
         2 | b            | e            | 700
         3 | c            | f            | 900