首页 > 代码库 > 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