频道栏目
IT货架 > > 正文
GROUPING SETS与GROUP_ID
网友分享于:Jan 1, 1970 8:00:00 AM    来源: IT货架   

标签:

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

标签:

广告服务联系QQ:1134687142 | 网站地图

版权所有: IT货架- 内容来自互联网,仅供用于技术学习,请遵循相关法律法规. 京ICP备11030978号-1