Mikrozadanie 4

backback

Treść

Wypisz dla każdego pracownika sumę zarobków jego i jego podwładnych.

Wzorcówka

Z WITH RECURSIVE:

WITH sub (empno, sal, root, root_ename) AS (
    SELECT empno, sal, empno AS root, ename AS root_ename
      FROM emp
    UNION ALL
      SELECT e.empno, e.sal, s.root, s.root_ename
        FROM emp e
        JOIN sub s
          ON e.mgr = s.empno
)
SELECT root AS empno, root_ename AS ename, SUM(sal) AS total
  FROM sub s
GROUP BY root, root_ename
ORDER BY SUM(sal), empno;

Z CONNECT BY:

SELECT empno, ename, (
  SELECT SUM(sal) 
    FROM emp e2
    START WITH e1.empno = e2.empno 
    CONNECT BY mgr = PRIOR empno
) AS total 
  FROM EMP e1
ORDER BY total, empno;

backback