我在Redshift中有这样的数据:
+-------------+------------+---------+
| Employee_ID | Manager_ID | Revenue |
+-------------+------------+---------+
| 123 | 123 | 1015.24 |
| 541 | 123 | 5587.23 |
+-------------+------------+---------+
我想要编写一个查询,每当输入Manager_ID时计算经理收入,每当输入Employee_ID时计算员工收入。目前,我有一个查询,看起来像这样,我必须运行它两次:
SELECT
sum(revenue) as revenue
FROM
employee_rev r
WHERE
r.manager_id in ('123','124') --I change this to employee_ID the second time around
如果有帮助,还有另一个这样的表:
+-------------+------------------------+
| Employee_ID | Role |
+-------------+------------------------+
| 123 | Manager |
| 541 | Individual Contributor |
+-------------+------------------------+
非常感谢你的时间,这看起来真的很简单,现在我很沮丧。
发布于 2018-08-10 05:48:35
我认为你可以这样做:
SELECT sum(revenue) as revenue
FROM employee_rev r
WHERE 123 in (r.employee_id, r.manager_id);
也就是说,对于给定的id,在两列中都进行查找。员工永远不应该出现在“经理”列中,因此这看起来像是在做您想做的事情。
编辑:
对于多个ids,您必须独立测试。以下任一项:
WHERE 123 IN (r.employee_id, r.manager_id) OR
456 IN (r.employee_id, r.manager_id)
或者:
WHERE r.employee_id in (123, 456) OR
r.manager_id in (123, 456)
发布于 2018-08-10 05:53:39
使用union将两个selects添加到一个'table‘中,然后对其求和。我想这应该行得通
SELECT sum(result) from (
SELECT
sum(revenue) as result
FROM
employee_rev r
WHERE
r.manager_id in ('123')
UNION ALL
SELECT
sum(revenue) as result
FROM
employee_rev r
WHERE
r.employee_id in ('124')
)
https://stackoverflow.com/questions/51776189
复制相似问题