SQL-Oracle-如何从联接结果中选择特定部门

Question

你们能帮我解决这个问题吗？我想修改它，以便它只显示来自此查询（78%）的至少3名员工的部门，而不是来自（原始的员工/部门表）。每次我尝试“having COUNT(department_ID)或@the WHERE子句”都会给我一个错误。我需要做第二次连接吗？谢谢。

select *
from
(
 SELECT b.employee_id, b.employee_name,b.salary, a.department_id, 
 NVL(a.department_name, 'N/A') as dept_name,
 max(b.salary) over (partition by a.department_id) as max_sal
FROM        department a, employee b
WHERE       a.department_id(+) = b.department_id
) z
WHERE salary > (max_sal*.78 )

   7566 JONES                      3000            10 ACCOUNTING                 3000 
   7886 STEEL                      2500            10 ACCOUNTING                 3000 
   7944 LEE                        2400            20 RESEARCH                   3000 
   7999 WOLFE                      2500            20 RESEARCH                   3000 
   7610 WILSON                     3000            20 RESEARCH                   3000 
   7921 JACKSON                    2500            30 SALES                      3000 
   7900 FISHER                     3000            30 SALES                      3000 
   7788 SCOTT                      2500            40 IT                         2900 
   7910 SMITH                      2900            40 IT                         2900 
   7603 CLARK                      4000            50 EXECUTIVE                  5000 
   7596 JOST                       4500            50 EXECUTIVE                  5000 
   7839 KING                       5000            50 EXECUTIVE                  5000 
   8000 BREWSTER                   2500               N/A                        2500 

         This are RESULTS I need to get:

EMPLOYEE_ID EMPLOYEE_NAME工资DEPARTMENT_ID DEPT_NAME MAX_SAL

   7944 LEE                        2400            20 RESEARCH                   3000 
   7999 WOLFE                      2500            20 RESEARCH                   3000 
   7610 WILSON                     3000            20 RESEARCH                   3000 
   7603 CLARK                      4000            50 EXECUTIVE                  5000 
   7596 JOST                       4500            50 EXECUTIVE                  5000 
   7839 KING                       5000            50 EXECUTIVE                  5000 

选定6行

Answer 1

您可以在另一个子查询中使用分析函数进行计算：

select de.*
from (select de.*, count(*) over (partition by department_id) as cnt
      from (SELECT e.employee_id, e.employee_name, d.salary, d.department_id, 
                   NVL(d.department_name, 'N/A') as dept_name,
                   max(e.salary) over (partition by d.department_id) as max_sal
            FROM department d JOIN
                 employee e
                 ON d.department_id = e.department_id
           ) de
      where salary > max_sal*.78
     ) de
where cnt >= 3;

外部连接似乎没有必要，所以我用内部连接和现代的join语法替换了它。我还将表别名改为表的缩写。它使代码更容易阅读。