Oracle sql-从两个不同的表中选择两个和之间的差异？

Question

假设我有两个表，我想计算它们的和差：

company1(
id INTEGER,
salary INTEGER
)

company2(
id INTEGER,
salary INTEGER
)

如何仅使用一个选择查询从每个员工中选择他们的SUM（工资）之间的差异？

例如：

SELECT 
(SELECT SUM(company1.salary) FROM company1 GROUP BY id) - (SELECT SUM(company2.salary) FROM company2 GROUP BY id) as diff_between_pay

最终结果将是这样的:

company1
id   salary  
1    40000
2    20000
3    50000

company2
id   salary
1    30000
2    15000
3    25000

end result differences:
id
1    10000
2    5000
3    25000

Answer 1

您可以在select中执行此操作：

select (select sum(salary) from company1) - (select sum(salary) from company2) as diff_between_pay
from dual;

对于修改后的问题，请使用完全联接：

select ( coalesce(c1.sums, 0) - coalesce(c2.sums, 0) ) as diff_between_pay
from (select id, sum(salary) as sums
      from company1
      group by id
     ) c1 full join
     (select id, sum(salary) as sums
      from company2
      group by id
     ) c2
     on c1.id = c2.id;