首页 > 代码库 > GROUPING SETS与GROUP_ID

GROUPING SETS与GROUP_ID

SQL> SELECT E.DEPARTMENT_ID DID,
  2         E.JOB_ID JOB,
  3         E.MANAGER_ID MID,
  4         SUM(E.SALARY) SUM_SAL,
  5         COUNT(E.EMPLOYEE_ID) CNT,
  6         GROUP_ID() GG
  7    FROM EMPLOYEES E
  8   WHERE E.JOB_ID IN (‘ST_CLERK‘, ‘SH_CLERK‘, ‘MK_REP‘, ‘MK_MAN‘)
  9   GROUP BY GROUPING SETS((E.DEPARTMENT_ID, E.JOB_ID), E.MANAGER_ID, E.JOB_ID)
 10   ORDER BY E.DEPARTMENT_ID, E.JOB_ID, E.MANAGER_ID;
  DID JOB        MID    SUM_SAL        CNT         GG
----- ---------- ------- ---------- ---------- ----------
   20 MK_MAN                  13000          1          0
   20 MK_REP                   6000           1           0
   50 SH_CLERK                64300         20          0
   50 ST_CLERK                55700         20          0
        MK_MAN                  13000          1          0
        MK_REP                   6000           1           0
        SH_CLERK                64300         20          0
        ST_CLERK                55700         20          0
                     100            13000          1          0
                     120            22100          8          0
                     121           25400           8          0
                     122           23600           8          0
                     123           25900           8          0
                     124           23000           8          0
                     201           6000             1          0

GROUPING SETS与GROUP_ID