(Oracle)使用相关子查询时获取多个列

Question

我(Scott)的目标是使用相关子查询获取经理的姓名、薪水和deptno。如下所示，我可以得到预期的结果，但是类似的子查询已经被使用了几次。有没有另一种整洁的方式(不重复类似的模式，如下所示)？

SELECT
    O.ENAME EMP_NAME
    ,(SELECT DISTINCT FIRST_VALUE(I.ENAME) OVER (PARTITION BY NULL ORDER BY I.SAL DESC)
        FROM SCOTT.EMP I
        WHERE I.EMPNO=O.MGR  --correlated to outer
     ) AS MGR_NAME
     ,(SELECT DISTINCT FIRST_VALUE(I.SAL) OVER (PARTITION BY NULL ORDER BY I.SAL DESC)
        FROM SCOTT.EMP I
        WHERE I.EMPNO=O.MGR  --correlated to outer
     ) AS MGR_SAL
     ,(SELECT DISTINCT FIRST_VALUE(I.DEPTNO) OVER (PARTITION BY NULL ORDER BY I.SAL DESC)
        FROM SCOTT.EMP I
        WHERE I.EMPNO=O.MGR  --correlated to outer
     ) AS MGR_DEPTNO
    from SCOTT.EMP O;

Answer 1

select  e.ename emp_name, m.ename mgr_name, m.sal mgr_sal, m.deptno mgr_deptno
from    emp e left join emp m
on      e.mgr = m.empno
order   by m.deptno, e.ename;

输出：

​

​