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

Question

我需要通过两个不同的表格来获得员工的工资：他的收益和折扣。从表雇员到两个表的关系是多对多关系。因此，我需要获取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的员工的工资。但是我怎样才能使它通用呢？我想给所有员工一份薪水。

Answer 1

我建议您使用两个CTE来计算收益和折扣，然后对这两个集合进行FULL OUTER JOIN。这将确保您获得适当的值，例如employee_id的丢失收益或折扣为0。如果你想忽略它们，这种情况只需将其更改为普通的INNER JOIN

CREATE OR REPLACE VIEW V_salary AS  --give proper name to indicate it's a view 
WITH ge 
AS (SELECT e.employee_id, 
SUM(g.value) AS gain_value 
FROM   gain_employee e 
JOIN gains g --use left join if some employees don't 
--have an entry in gains
ON e.gain_id = g.gain_id 
GROUP  BY e.employee_id), 
de 
AS (SELECT e.employee_id, 
SUM(d.value) AS dis_value
FROM   discount_employee e  
JOIN discounts d --use left join if some employees don't 
--have an entry in discount
ON  e.discount_id = d.discount_id 
GROUP  BY e.employee_id) 
SELECT COALESCE(ge.employee_id, gd.employee_id), --gets you atleast one of 
--them when one may be missing. 
COALESCE(ge.gain_value, 0) - COALESCE(de.dis_value, 0) AS salary 
FROM   ge 
FULL OUTER JOIN de    -- to consider case where one of them is absent
ON ge.employee_id = de.employee_id;

Answer 2

这应该可以做到-

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为条件查询此视图。