sql - (Oracle)Getting multiple column when using correlated subquery

Question

My(Scott) goal is to get manager's name ,salary and deptno using correlated subquery. As below, I could get expected result, but similar subquery has been used several times. Is there a another neat way(without repeated similar pattern as below)?

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;

标签： sqloraclejoinsubquery

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;

输出：