我正在使用Oracle。我有表名的作品看起来像:
Company-name Employee-name salary
A1 Jim 122000
...
表2的管理外观(经理自行管理)
Employee-name Manager-name
Jim John
Kate John
John John
我想以利润=收入总额的工资返回公司,其中收入=雇员的工资* 3(这里,只有雇员产生收入,而经理没有)。这是我的代码
with Employee_count as(
Select a.company-name, b.employee-name as Guru
From works a
Inner join manages b
On a.employee-name=b.employee-name
Where b.employee-name<>b.manager-name
Group by a.company-name
)
Comput_revenue as (
select a2.company-name, SUM(3*a2.salary) as Revenue
from works a2
Inner join Employee_count b2
On a2.company-name=b2.company-name
Where a2.employee-name=b2.Guru
group by a2.company-name),
select a3.company-name, b3.Revenue-SUM(a3.salary) as Profit
from works a3 inner join Comput_revenue b3
On a3.company-name=b3.company-name;
我在这里有两个问题:
(1)表名的别名是否在每个子句中都是本地的?我应该使用a,a2,a3还是只使用a和b都可以?
(2)如果正确,它看起来仍然太大。有任何简化的想法吗?
我希望使用WITH子句不是强制性的,因为我为您找到了一个更简单的解决方案:
作品
COMPANY EMPLOYEE_NAME SALARY A1吉姆122000 A1凯特132000 A1约翰88000 A2杰克96000 A2杰森134000 A2珍妮特138000
管理
EMPLOYEE_NAME MANAGER_NAME Jim John Kate John John John John Jack Janet Jason Janet Janet Janet
实现您的逻辑的查询是:
SELECT
COMPANY,
SUM(
DECODE(EMPLOYEE_NAME, MANAGER_NAME, 0, SALARY * 3) - SALARY
) AS PROFIT
FROM WORKS
INNER JOIN MANAGES USING(EMPLOYEE_NAME)
GROUP BY COMPANY
ORDER BY 2 DESC;
公司利润 A1 420000 A2 322000
您的方法不错,但是您开始专注于使用CTE(WITH子句)功能。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句