database - 通过两个不同的表获取派生属性

Question

我需要通过两个不同的表来获取员工的工资：他的收益和折扣。从表employee 到两个表的关系是多对多关系。所以我需要获取employee_id，获取employee_gains 表中的所有gain_id，将它们全部相加并减去折扣中的模拟结果。

我试过这个为工资创建一个视图：

  CREATE VIEW salary as 
select ((select sum(value) from gains 
where gain_id in (select gain_id from gain_employee where employee_id=2)) 
- 
(select sum(value) from discount 
where discount_id in (select gain_id from discount_employee where employee_id=2)));

但是，这只是（并且成功地）给了我 ID 为 2 的员工的薪水。但是我怎样才能使这个通用呢？我想要查看所有员工的工资。

标签： databasepostgresql

Answer 1

这应该这样做 -

CREATE VIEW salary as
select S1.employee_id, (S1.gains - S2.discounts) as salary
    from (select ge.employee_id, sum(g.value) as gains 
              from gain_employee ge, gains g
              where ge.gain_id = g.gain_id
              group by ge.employee_id) S1,
         (select de.employee_id, sum(d.value) as discounts 
              from discount_employee de, discounts d
              where de.doscount_id = d.discount_id
              group by de.employee_id) S2
    where S1.employee_id = S2.employee_id;

然后你可以查询这个视图employee_id作为条件。