oracle rollup 排序,【ROLLUP】Oracle分组函数之ROLLUP魅力

本文通过演示给出Oracle ROLLUP分组函数的用法，体验一下Oracle在统计查询领域中的函数魅力。ROLLUP分组函数可以理解为Group By分组函数封装后的精简用法，这里同时给出ROLLUP的Group By的改写思路。1.初始化实验坏境1)创建测试表group_testSECOOLER@ora11g> create table group_test (group_id int, job varchar2(10), name varchar2(10), salary int);Table created.2)初始化数据insert into group_test values (10,'Coding',    'Bruce',1000);insert into group_test values (10,'Programmer','Clair',1000);insert into group_test values (10,'Architect', 'Gideon',1000);insert into group_test values (10,'Director',  'Hill',1000);insert into group_test values (20,'Coding',    'Jason',2000);insert into group_test values (20,'Programmer','Joey',2000);insert into group_test values (20,'Architect', 'Martin',2000);insert into group_test values (20,'Director',  'Michael',2000);insert into group_test values (30,'Coding',    'Rebecca',3000);insert into group_test values (30,'Programmer','Rex',3000);insert into group_test values (30,'Architect', 'Richard',3000);insert into group_test values (30,'Director',  'Sabrina',3000);insert into group_test values (40,'Coding',    'Samuel',4000);insert into group_test values (40,'Programmer','Susy',4000);insert into group_test values (40,'Architect', 'Tina',4000);insert into group_test values (40,'Director',  'Wendy',4000);commit;3)初始化之后的数据情况如下：SECOOLER@ora11g> set pages 100SECOOLER@ora11g> select * from group_test;GROUP_ID JOB        NAME           SALARY---------- ---------- ---------- ----------10 Coding     Bruce            100010 Programmer Clair            100010 Architect  Gideon           100010 Director   Hill             100020 Coding     Jason            200020 Programmer Joey             200020 Architect  Martin           200020 Director   Michael          200030 Coding     Rebecca          300030 Programmer Rex              300030 Architect  Richard          300030 Director   Sabrina          300040 Coding     Samuel           400040 Programmer Susy             400040 Architect  Tina             400040 Director   Wendy            400016 rows selected.2.先看一下普通分组的效果：对group_id进行普通的group by操作---按照小组进行分组SECOOLER@ora11g> select group_id,sum(salary) from group_test group by group_id;GROUP_ID SUM(SALARY)---------- -----------30       1200020        800040       1600010        40003.对group_id进行普通的roolup操作---按照小组进行分组，同时求总计SECOOLER@ora11g> select group_id,sum(salary) from group_test group by rollup(group_id);GROUP_ID SUM(SALARY)---------- -----------10        400020        800030       1200040       1600040000使用Group By语句翻译一下上面的SQL语句如下(union all一个统计所有数据的行)：SECOOLER@ora11g> select group_id,sum(salary) from group_test group by group_id2  union all3  select null, sum(salary) from group_test4  order by 1;GROUP_ID SUM(SALARY)---------- -----------10        400020        800030       1200040       16000400004.再看一个rollup两列的情况SECOOLER@ora11g> select group_id,job,sum(salary) from group_test group by rollup(group_id, job);GROUP_ID JOB        SUM(SALARY)---------- ---------- -----------10 Coding            100010 Director          100010 Architect         100010 Programmer        100010                   400020 Coding            200020 Director          200020 Architect         200020 Programmer        200020                   800030 Coding            300030 Director          300030 Architect         300030 Programmer        300030                  1200040 Coding            400040 Director          400040 Architect         400040 Programmer        400040                  160004000021 rows selected.上面的SQL语句该如何使用Group By进行翻译呢？答案如下：SECOOLER@ora11g> select group_id,job,sum(salary) from group_test group by group_id, job2  union all3  select group_id,null,sum(salary) from group_test group by group_id4  union all5  select null,null,sum(salary) from group_test6  order by 1,2;GROUP_ID JOB        SUM(SALARY)---------- ---------- -----------10 Architect         100010 Coding            100010 Director          100010 Programmer        100010                   400020 Architect         200020 Coding            200020 Director          200020 Programmer        200020                   800030 Architect         300030 Coding            300030 Director          300030 Programmer        300030                  1200040 Architect         400040 Coding            400040 Director          400040 Programmer        400040                  160004000021 rows selected.5.补充一步，体验一下GROUPING函数的效果直接看效果就OK啦：SECOOLER@ora11g> select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test group by rollup(group_id, job);GROUP_ID JOB        GROUPING(GROUP_ID) GROUPING(JOB) SUM(SALARY)---------- ---------- ------------------ ------------- -----------10 Coding                      0             0        100010 Director                    0             0        100010 Architect                   0             0        100010 Programmer                  0             0        100010                             0             1        400020 Coding                      0             0        200020 Director                    0             0        200020 Architect                   0             0        200020 Programmer                  0             0        200020                             0             1        800030 Coding                      0             0        300030 Director                    0             0        300030 Architect                   0             0        300030 Programmer                  0             0        300030                             0             1       1200040 Coding                      0             0        400040 Director                    0             0        400040 Architect                   0             0        400040 Programmer                  0             0        400040                             0             1       160001             1       4000021 rows selected.看出来什么效果了么？有的同学还是没有看出来，小小的解释一下：如果显示“1”表示GROUPING函数对应的列(例如JOB字段)是由于ROLLUP函数所产生的空值对应的信息，即对此列进行汇总计算后的结果。如果显示“0”表示此行对应的这列参未与ROLLUP函数分组汇总活动。如果还是没有理解清楚，请参见Oracle官方文档中的描述内容：“Using a single column as its argument,GROUPINGreturns 1 when it encounters aNULLvalue created by aROLLUPorCUBEoperation. That is, if theNULLindicates the row is a subtotal,GROUPINGreturns a 1. Any other type of value, including a storedNULL, returns a 0.