始创于2000年 股票代码：831685
咨询热线：0371-60135900 注册有礼 登录
  • 挂牌上市企业
  • 60秒人工响应
  • 99.99%连通率
  • 7*24h人工
  • 故障100倍补偿
您的位置： 网站首页 > 帮助中心>文章内容

帮助类别

帮助中心

新手上路新手上路 代理登陆代理登陆 常见问题常见问题 备案咨询备案咨询 投诉建议投诉建议 速度测试速度测试 我要评价我要评价 加盟合作加盟合作 常用文档常用文档

文章内容

Oracle 中 rollup、cube、grouping 使用详解
发布时间:  2012/8/26 15:38:06
1.-- 使用Oracle 的样列库，演示 rollup, cube, grouping 的用法与使用场景  
2. 
3.--- ROLLUP ， 为了理解分组的成员数量，我增加了 分组的计数  COUNT(SAL)   4.  SELECT   E1.DEPTNO,  5.           JOB, 
6.           TO_CHAR (E1.HIREDATE, 'YYYY-MM-DD'),  7.           SUM (SAL),  8.           COUNT (SAL)  9.    FROM   emp e1  10.GROUP BY   ROLLUP (E1.DEPTNO, E1.JOB, E1.HIREDATE);  11. 
12./* 
13.分组情况为: 
14.DEPTNO，job,HIREDATE   第 1 种分组  (A,B,C)  
15.DEPTNO，job,           第 2 种分组  (A,B)   
16.DEPTNO                 第 3 种分组  (A)   
17.                       总分一个组 
18.                        
19.-- 结果如下：  并进行部分数据解释，以便读者理解   20.    DEPTNO JOB       TO_CHAR(E1   SUM(SAL) COUNT(SAL)  21.---------- --------- ---------- ---------- ----------   22.        10 CLERK     1982-01-23       1300          1   (A,B,C)  -- 10号部门,CLERK工种，入职日期为1982-01-23，只有一个成员 工资和为：1300   23.        10 CLERK                      1300          1   (A,B)    -- 10号部门,CLERK工种，只有一个成员 工资和为：1300   24.        10 MANAGER   1981-06-09       2450          1   (A,B,C)  -- 与第一条记录相似的分析    25.        10 MANAGER                    2450          1   (A,B) 
26.        10 PRESIDENT 1981-11-17       5000          1   (A,B,C)   
27.        10 PRESIDENT                  5000          1   (A,B,C) 
28.        10                            8750          3   (A)      -- 10号部门有3个成员，工资总计为8750   29.        20 CLERK     1980-12-17        800          1 
30.        20 CLERK                       800          1 
31.        20 ANALYST   1981-12-03       3000          1 
32.        20 ANALYST                    3000          1 
33.        20 MANAGER   1981-04-02       2975          1 
34.        20 MANAGER                    2975          1 
35.        20                            6775          3 
36.        30 CLERK     1981-12-03        950          1 
37.        30 CLERK                       950          1 
38.        30 MANAGER   1981-05-01       2850          1 
39.        30 MANAGER                    2850          1 
40.        30 SALESMAN  1981-02-20       1600          1 
41.        30 SALESMAN  1981-02-22       1250          1 
42.        30 SALESMAN  1981-09-08       1500          1 
43.        30 SALESMAN  1981-09-28       1250          1 
44.        30 SALESMAN                   5600          4    (A,B)    -- 30号部门,SALESMAN工种，有4个成员 工资和为：5600    45.        30                            9400          6    (A)      -- 30号部 有6个成员， 工资总计为：9400    46.                                     24925         12     ()      -- 所有部门工资总和为：24925                           47.*/ 
48. 
49. 
50.--- CUBE ， 为了理解分组的成员数量，我增加了 分组的计数  COUNT(SAL)   51.  SELECT   E1.DEPTNO,  52.           JOB, 
53.           TO_CHAR (E1.HIREDATE, 'YYYY-MM-DD'),  54.           SUM (SAL),  55.           COUNT (SAL)  56.    FROM   emp e1  57.GROUP BY   CUBE (E1.DEPTNO, E1.JOB, E1.HIREDATE);  58. 
59./* 
60.分组原则： 
61.GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。  62. 
63.-- 结果 ：  并进行部分数据解释，以便读者理解   64.    DEPTNO JOB       TO_CHAR(E1   SUM(SAL) COUNT(SAL)  65.---------- --------- ---------- ---------- ----------   66.                                     24925         12   -- 全表分组，工资总合:24925    67.                     1980-12-17        800          1    
68.                     1981-02-20       1600          1 
69.                     1981-02-22       1250          1 
70.                     1981-04-02       2975          1 
71.                     1981-05-01       2850          1 
72.                     1981-06-09       2450          1 
73.                     1981-09-08       1500          1 
74.                     1981-09-28       1250          1 
75.                     1981-11-17       5000          1 
76.                     1981-12-03       3950          2   (C)  1981-12-03 入职的有2位员工，工资总计3950 
77.                     1982-01-23       1300          1 
78.           CLERK                      3050          3   (B) CLERK 工种，共计有3位员工，工资总计：3050 
79.           CLERK     1980-12-17        800          1 
80.           CLERK     1981-12-03        950          1 
81.           CLERK     1982-01-23       1300          1 
82.           ANALYST                    3000          1 
83.           ANALYST   1981-12-03       3000          1 
84.           MANAGER                    8275          3 
85.           MANAGER   1981-04-02       2975          1 
86.           MANAGER   1981-05-01       2850          1 
87.           MANAGER   1981-06-09       2450          1 
88.           SALESMAN                   5600          4 
89.           SALESMAN  1981-02-20       1600          1 
90.           SALESMAN  1981-02-22       1250          1 
91.           SALESMAN  1981-09-08       1500          1 
92.           SALESMAN  1981-09-28       1250          1 
93.           PRESIDENT                  5000          1 
94.           PRESIDENT 1981-11-17       5000          1 
95.        10                            8750          3 
96.        10           1981-06-09       2450          1 
97.        10           1981-11-17       5000          1 
98.        10           1982-01-23       1300          1 
99.        10 CLERK                      1300          1 
100.        10 CLERK     1982-01-23       1300          1 
101.        10 MANAGER                    2450          1 
102.        10 MANAGER   1981-06-09       2450          1 
103.        10 PRESIDENT                  5000          1 
104.        10 PRESIDENT 1981-11-17       5000          1 
105.        20                            6775          3  (A) -- 20号部 有3个成员， 工资总计为：6775    106.        20           1980-12-17        800          1 
107.        20           1981-04-02       2975          1 
108.        20           1981-12-03       3000          1 
109.        20 CLERK                       800          1 
110.        20 CLERK     1980-12-17        800          1 
111.        20 ANALYST                    3000          1 
112.        20 ANALYST   1981-12-03       3000          1 
113.        20 MANAGER                    2975          1 
114.        20 MANAGER   1981-04-02       2975          1 
115.        30                            9400          6  (A) -- 30号部 有6个成员， 工资总计为：9400    116.        30           1981-02-20       1600          1 
117.        30           1981-02-22       1250          1 
118.        30           1981-05-01       2850          1 
119.        30           1981-09-08       1500          1 
120.        30           1981-09-28       1250          1 
121.        30           1981-12-03        950          1 
122.        30 CLERK                       950          1 
123.        30 CLERK     1981-12-03        950          1 
124.        30 MANAGER                    2850          1 
125.        30 MANAGER   1981-05-01       2850          1 
126.        30 SALESMAN                   5600          4  (A、B) 30号部门，  SALESMAN 工种，有4 个成员，工资总计：5600 
127.        30 SALESMAN  1981-02-20       1600          1   
128.        30 SALESMAN  1981-02-22       1250          1 
129.        30 SALESMAN  1981-09-08       1500          1  (A、B、C)  0号部门，  SALESMAN 工种，1981-09-08入职，1 个员工，工资总计：1500 
130.        30 SALESMAN  1981-09-28       1250          1  (A、B、C)  0号部门，  SALESMAN 工种，1981-09-28入职，1 个员工，工资总计：1250 
131. 
132.已选择65行。 
133.*/ 
134. 
135.--- GROUPING函数   136./* 
137.GROUPING 是一个聚合函数,它产生一个附加的列，当用 CUBE 或 ROLLUP 运算符添加行时，附加的列输出值为1，当所添加的行不是由 CUBE 或 ROLLUP 产生时，附加列值为0。  138.仅在与包含 CUBE 或 ROLLUP 运算符的 GROUP BY 子句相联系的选择列表中才允许分组。  139. 
140.语法:  GROUPING ( column_name )  141. 
142.是 GROUP BY 子句中用于检查 CUBE 或 ROLLUP 空值的列。  143. 
144.返回类型: int  145. 
146.分组用于区分由 CUBE 和 ROLLUP 返回的空值和标准的空值。作为CUBE 或 ROLLUP 操作结果返回的 NULL 是 NULL 的特殊应用。它在结果集内作为列的占位符，意思是"全体"。  147.*/ 
148. 
149.-- grouping 样列   150.  SELECT   E1.DEPTNO,  151.           JOB, 
152.           TO_CHAR (E1.HIREDATE, 'YYYY-MM-DD') HIREDATE,  153.           SUM (SAL),  154.           COUNT (SAL),  155.           GROUPING (E1.DEPTNO) d,  156.           GROUPING (JOB) j,  157.           GROUPING (E1.HIREDATE) h  158.    FROM   emp e1  159.GROUP BY   ROLLUP (E1.DEPTNO, E1.JOB, E1.HIREDATE);  160. 
161. 
162. 
163./* 
164.-- 结果 ：  并进行部分数据解释，以便读者理解   165. 
166.    DEPTNO JOB       HIREDATE     SUM(SAL) COUNT(SAL)   D   J   H  167.---------- --------- ---------- ---------- ---------- --- --- ---   168.        10 CLERK     1982-01-23       1300          1   0   0   0   所有列都有数据作为分组，所以全为 0 
169.        10 CLERK                      1300          1   0   0   1   DEPTNO JOB列 有数据，而HIREDATE没有数据，所以 H 列产生的值为：1 
170.        10 MANAGER   1981-06-09       2450          1   0   0   0     
171.        10 MANAGER                    2450          1   0   0   1 
172.        10 PRESIDENT 1981-11-17       5000          1   0   0   0 
173.        10 PRESIDENT                  5000          1   0   0   1 
174.        10                            8750          3   0   1   1 
175.        20 CLERK     1980-12-17        800          1   0   0   0 
176.        20 CLERK                       800          1   0   0   1 
177.        20 ANALYST   1981-12-03       3000          1   0   0   0 
178.        20 ANALYST                    3000          1   0   0   1 
179.        20 MANAGER   1981-04-02       2975          1   0   0   0 
180.        20 MANAGER                    2975          1   0   0   1 
181.        20                            6775          3   0   1   1 
182.        30 CLERK     1981-12-03        950          1   0   0   0 
183.        30 CLERK                       950          1   0   0   1 
184.        30 MANAGER   1981-05-01       2850          1   0   0   0 
185.        30 MANAGER                    2850          1   0   0   1 
186.        30 SALESMAN  1981-02-20       1600          1   0   0   0 
187.        30 SALESMAN  1981-02-22       1250          1   0   0   0 
188.        30 SALESMAN  1981-09-08       1500          1   0   0   0 
189.        30 SALESMAN  1981-09-28       1250          1   0   0   0 
190.        30 SALESMAN                   5600          4   0   0   1 
191.        30                            9400          6   0   1   1 
192.                                     24925         12   1   1   1    
193.*/ 
194. 
195. 
196.-- 应用 grouping     197.SELECT   CASE  198.              WHEN (    GROUPING (E1.DEPTNO) = 0 AND GROUPING (JOB) = 0 AND GROUPING (HIREDATE) = 0)  199.                   THEN DEPTNO|| ' '|| JOB|| ' ' || TO_CHAR (HIREDATE, 'YYYY-MM-DD')|| ' subtotal:'  200.              WHEN (    GROUPING (E1.DEPTNO) = 0 AND GROUPING (JOB) = 0 AND GROUPING (HIREDATE) = 1)  201.                   THEN DEPTNO || ' ' || JOB || ' subtotal:'  202.              WHEN (    GROUPING (E1.DEPTNO) = 0 AND GROUPING (JOB) = 1 AND GROUPING (HIREDATE) = 1)  203.                   THEN DEPTNO || ' subtotal:'  204.              WHEN (    GROUPING (E1.DEPTNO) = 1 AND GROUPING (JOB) = 1 AND GROUPING (HIREDATE) = 1)  205.                   THEN 'Total:'  206.           END  207.              "Total",   208.           SUM (SAL), COUNT (SAL)  209.    FROM   emp e1  210.GROUP BY   ROLLUP (E1.DEPTNO, E1.JOB, E1.HIREDATE);  211. 
212.-- 结果如下：   213./* 
214.Total                                  SUM(SAL) COUNT(SAL)  215.------------------------------------ ---------- ----------   216.10 CLERK 1982-01-23 subtotal:              1300          1   (A,B,C) 分组 
217.10 CLERK subtotal:                         1300          1   (A,B) 分组 
218.10 MANAGER 1981-06-09 subtotal:            2450          1 
219.10 MANAGER subtotal:                       2450          1 
220.10 PRESIDENT 1981-11-17 subtotal:          5000          1 
221.10 PRESIDENT subtotal:                     5000          1 
222.10 subtotal:                               8750          3  (A) 分组 10号部门，共3个成员，工资总计：8750 
223.20 CLERK 1980-12-17 subtotal:               800          1 
224.20 CLERK subtotal:                          800          1 
225.20 ANALYST 1981-12-03 subtotal:            3000          1 
226.20 ANALYST subtotal:                       3000          1 
227.20 MANAGER 1981-04-02 subtotal:            2975          1 
228.20 MANAGER subtotal:                       2975          1 
229.20 subtotal:                               6775          3 
230.30 CLERK 1981-12-03 subtotal:               950          1 
231.30 CLERK subtotal:                          950          1 
232.30 MANAGER 1981-05-01 subtotal:            2850          1 
233.30 MANAGER subtotal:                       2850          1 
234.30 SALESMAN 1981-02-20 subtotal:           1600          1 
235.30 SALESMAN 1981-02-22 subtotal:           1250          1 
236.30 SALESMAN 1981-09-08 subtotal:           1500          1 
237.30 SALESMAN 1981-09-28 subtotal:           1250          1 
238.30 SALESMAN subtotal:                      5600          4 
239.30 subtotal:                               9400          6 
240.Total:                                    24925         12  () 全部总计：12 个成员，工资总计为：24925 
241. 
242.已选择25行。 
243.*/ 

本文出自：亿恩科技【www.enkj.com】

服务器租用/服务器托管中国五强！虚拟主机域名注册顶级提供商！15年品质保障！--亿恩科技[ENKJ.COM]

同类文章

  • 您可能在找
  • 域名购买
  • 服务器托管/租用
  • 虚拟主机
  • 云主机
  • 亿恩云建站
  • 亿恩北京公司：
  • 经营性ICP/ISP证：京B2-20150015
    •
  • 亿恩郑州公司：
  • 经营性ICP/ISP/IDC证：豫B1.B2-20060070
    •
  • 亿恩南昌公司：
  • 经营性ICP/ISP证：赣B2-20080012
    •
  • 服务器/云主机 24小时售后服务电话：0371-60135900
  • 虚拟主机/智能建站 24小时售后服务电话：0371-60135900
    •
    专注服务器托管17年
    扫扫关注-微信公众号
    0371-60135900
    Copyright© 1999-2019 ENKJ All Rights Reserved 亿恩科技 版权所有　 地址：郑州市高新区翠竹街1号总部企业基地亿恩大厦　 法律顾问：河南亚太人律师事务所郝建锋、杜慧月律师   京公网安备41019702002023号
      0

    亿恩公告

    查看更多
     
     

    在线客服

     
     

    0371-60135900
    7*24小时客服服务热线