ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于 group by 子句的几个实验

关于 group by 子句的几个实验

原创 Linux操作系统 作者:红叶DBA 时间:2011-03-19 15:20:46 0 删除 编辑
SCOTT:145@hongye > select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

14 rows selected.

例子1:普通的 Group by 子句
SCOTT:145@hongye > select deptno,sum(sal) from emp group by deptno;

    DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        20      10875
        10       8750

例子2:带有 rollup 函数的 Group by子句,可以返回rollup中指定列的总计信息
SCOTT:145@hongye > select deptno,sum(sal) from emp group by rollup(deptno);

    DEPTNO   SUM(SAL)
---------- ----------
        10       8750
        20      10875
        30       9400
                29025

例子3:带有 rollup 函数的 Group by子句,
其中 rollup 带有两个参数,
那么先以第一个参数为准,返回第二个参数的小计信息,
然后在最后返回所有信息的总计信息
SCOTT:145@hongye > select deptno,job,sum(sal) from emp group by rollup(deptno,job);

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        10                 8750
        20 CLERK           1900
        20 ANALYST         6000
        20 MANAGER         2975
        20                10875
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600
        30                 9400
                          29025

13 rows selected.

例子4:带有 cube 函数的 Group by子句,
可以返回cube中指定的各个列的总计信息,并返回一个总计信息
SCOTT:145@hongye > select deptno,job,sum(sal) from emp group by cube(deptno,job) order by deptno,job;

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        10                 8750
        20 ANALYST         6000
        20 CLERK           1900
        20 MANAGER         2975
        20                10875
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600
        30                 9400
           ANALYST         6000
           CLERK           4150
           MANAGER         8275
           PRESIDENT       5000
           SALESMAN        5600
                          29025

18 rows selected.

例子5:使用 grouping 函数判断哪一列的信息是属于 小计或总计的信息。
grouping(deptno),若是deptno列中遇到小计的行信息,那么此行的 grouping(deptno) 显示为1
注意 else deptno||''中,必须要使用||'',否则会报错:
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
这是因为在这里必须使用字符串的变量,而deptno是number类型的,所以不能通过。
SCOTT:145@hongye > select case grouping(deptno) when 1 then 'SUM' else deptno||'' end as deptno,sum(sal) from emp group by rollup(deptno);

DEPTNO                                     SUM(SAL)
---------------------------------------- ----------
10                                             8750
20                                            10875
30                                             9400
SUM                                           29025

同理:如果不使用 case when 语句而使用 decode,也必须使用 deptno||''
SCOTT:145@hongye > select decode(grouping(deptno),0,deptno||'','SUM'),sum(sal) from emp group by rollup(deptno);

DECODE(GROUPING(DEPTNO),0,DEPTNO||'','SU   SUM(SAL)
---------------------------------------- ----------
10                                             8750
20                                            10875
30                                             9400
SUM                                           29025

例子6:使用 grouping sets 函数,在group by 语句中使用 grouping sets可以只返回小计信息
group by grouping sets(deptno,job),则只返回了deptno和job列的小计信息。
SCOTT:145@hongye > select deptno,job,sum(sal) from emp group by grouping sets(deptno,job);

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
           CLERK           4150
           SALESMAN        5600
           PRESIDENT       5000
           MANAGER         8275
           ANALYST         6000
        30                 9400
        20                10875
        10                 8750

8 rows selected.

例子7:grouping_id函数的使用,grouping_id是grouping 的位向量
grouping_id(deptno,job),此时:
如果grouping(deptno)=0,grouping_id(job)=0,则grouping_id=[00](2进制)=0(10进制)
如果grouping(deptno)=0,grouping_id(job)=1,则grouping_id=[01](2进制)=1(10进制)
如果grouping(deptno)=1,grouping_id(job)=0,则grouping_id=[10](2进制)=2(10进制)
如果grouping(deptno)=1,grouping_id(job)=1,则grouping_id=[11](2进制)=3(10进制)
SCOTT:145@hongye > select deptno,job,grouping(deptno) grp1,grouping(job) grp2,grouping_id(deptno,job) grp3,sum(sal) from emp group by cube(deptno,job) order by deptno,job;

    DEPTNO JOB             GRP1       GRP2       GRP3   SUM(SAL)
---------- --------- ---------- ---------- ---------- ----------
        10 CLERK              0          0          0       1300
        10 MANAGER            0          0          0       2450
        10 PRESIDENT          0          0          0       5000
        10                    0          1          1       8750
        20 ANALYST            0          0          0       6000
        20 CLERK              0          0          0       1900
        20 MANAGER            0          0          0       2975
        20                    0          1          1      10875
        30 CLERK              0          0          0        950
        30 MANAGER            0          0          0       2850
        30 SALESMAN           0          0          0       5600
        30                    0          1          1       9400
           ANALYST            1          0          2       6000
           CLERK              1          0          2       4150
           MANAGER            1          0          2       8275
           PRESIDENT          1          0          2       5000
           SALESMAN           1          0          2       5600
                              1          1          3      29025

18 rows selected.

例子8:使用grouping_id 和 cube 也可以只返回小计信息,只要加上条件 grouping_id>0 即可
但是此方法的效率不如 grouping sets函数。
SCOTT:145@hongye > select deptno,job,grouping(deptno) grp1,grouping(job) grp2,grouping_id(deptno,job) grp3,sum(sal) from emp group by cube(deptno,job) having grouping_id(deptno,job)>0 order by deptno,job;

    DEPTNO JOB             GRP1       GRP2       GRP3   SUM(SAL)
---------- --------- ---------- ---------- ---------- ----------
        10                    0          1          1       8750
        20                    0          1          1      10875
        30                    0          1          1       9400
           ANALYST            1          0          2       6000
           CLERK              1          0          2       4150
           MANAGER            1          0          2       8275
           PRESIDENT          1          0          2       5000
           SALESMAN           1          0          2       5600
                              1          1          3      29025

9 rows selected.

SCOTT:145@hongye > select deptno,job,sum(sal) from emp group by grouping sets(deptno,job);

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
           CLERK           4150
           SALESMAN        5600
           PRESIDENT       5000
           MANAGER         8275
           ANALYST         6000
        30                 9400
        20                10875
        10                 8750

8 rows selected.

例子9:同一个列可以在 group by 子句中使用多次。
SCOTT:145@hongye > select deptno,job,sum(sal) from emp group by deptno,rollup(deptno,job) order by deptno,job;

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        10                 8750
        10                 8750
        20 ANALYST         6000
        20 CLERK           1900
        20 MANAGER         2975
        20                10875
        20                10875
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600
        30                 9400
        30                 9400

15 rows selected.

例子10:在例子9中,可以发现,每个小计的信息都是重复出现的,都出现了2遍,
可以使用group_id函数来消除此现象
SCOTT:145@hongye > select deptno,job,group_id(),sum(sal) from emp group by deptno,rollup(deptno,job) order by deptno,job;

    DEPTNO JOB       GROUP_ID()   SUM(SAL)
---------- --------- ---------- ----------
        10 CLERK              0       1300
        10 MANAGER            0       2450
        10 PRESIDENT          0       5000
        10                    1       8750
        10                    0       8750
        20 ANALYST            0       6000
        20 CLERK              0       1900
        20 MANAGER            0       2975
        20                    1      10875
        20                    0      10875
        30 CLERK              0        950
        30 MANAGER            0       2850
        30 SALESMAN           0       5600
        30                    1       9400
        30                    0       9400

15 rows selected.

可以看出,每次重读,group_id 都会加1,如果一条记录重复了n遍,那么就会有0~(n-1)个不同的 group_id
使用 group_id=0 就可以剔除掉重复的记录
SCOTT:145@hongye > select deptno,job,group_id(),sum(sal) from emp group by deptno,rollup(deptno,job) having group_id()=0 order by deptno,job;

    DEPTNO JOB       GROUP_ID()   SUM(SAL)
---------- --------- ---------- ----------
        10 CLERK              0       1300
        10 MANAGER            0       2450
        10 PRESIDENT          0       5000
        10                    0       8750
        20 ANALYST            0       6000
        20 CLERK              0       1900
        20 MANAGER            0       2975
        20                    0      10875
        30 CLERK              0        950
        30 MANAGER            0       2850
        30 SALESMAN           0       5600
        30                    0       9400

12 rows selected.

SCOTT:145@hongye > 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24465008/viewspace-689976/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 层次查询 小实验
请登录后发表评论 登录
全部评论

注册时间:2010-08-19

  • 博文量
    54
  • 访问量
    69653