sql - 使用 oracle 示例数据库简化查询

Question

任务:列出每个部门薪酬最高和最低的员工的姓名和薪水。您也可以分别执行最高和最低的查询。

尝试查询:

SELECT dept.deptno, 
       dname, 
       minsal, 
       maxsal 
FROM   dept, 
       (SELECT deptno, 
               Max (sal) MAXSAL 
        FROM   emp 
        GROUP  BY deptno) MAXSALARY, 
       (SELECT deptno, 
               Min (sal)MINSAL 
        FROM   emp 
        GROUP  BY deptno) MINSALARY 
WHERE  MAXSALARY.deptno = dept.deptno 
       AND MINSALARY.deptno = dept.deptno; 

结果

结果正确。

问题) 有没有其他方法可以简化查询？

Answer 1

您可以使用 ORACLE 的窗口函数，如 row_number 结合使用 CASE EXPRESSION 的条件聚合:

SELECT t.ename,t.dname,
       MAX(CASE WHEN t.low_ind = 1 then t.salary END),
       MAX(CASE WHEN t.high_ind = 1 then t.salary END)
FROM (SELECT e.name as ename,d.name as dname,e.salary, 
             ROW_NUMBER() OVER(PARTITION BY d.name ORDER BY e.salary ASC) as low_ind,
             ROW_NUMBER() OVER(PARTITION BY d.name ORDER BY e.salary DESC) as high_ind 
      FROM emp e
      INNER JOIN dept d
       ON(d.deptno = e.deptno)) t
GROUP BY t.ename,t.dname

编辑:如果您只需要每个部门的最低和最高工资，那么这就是一个简单的 group by 子句:

SELECT d.deptno,d.dname,MIN(e.salary) as min_sal,MAX(e.salary) as max_sal
FROM dept d
INNER JOIN emp e
 ON(d.deptno = e.deptno)
GROUP BY d.deptno,d.name