Oracle的group by分组,子句增强

开篇还是想吐槽一下这个Oracle的group by分组,没有MySQL那么好用,可能是MySQL用习惯了 :- (

表以及数据:

create table employees
(
  employee_id number(8) not null,         --员工id
  manager_id number(6),                   --经理id
  salary number(8,2),                     --工资
  employees_name varchar2(25) not null,   --员工名字
  entry_date date not null                --入职时间
);


insert into employees values (1000,100,10000,'jack1',sysdate -365);
insert into employees values (1001,101,10000,'jack2',sysdate -200);
insert into employees values (1002,101,8000,'jack3',sysdate -100);
insert into employees values (1003,102,8500,'jack4',sysdate -150);
insert into employees values (1004,102,8000,'jack5',sysdate -100);
insert into employees values (1005,102,8000,'jack6',sysdate -100);
insert into employees values (1006,100,8000,'jack7',sysdate -100);
insert into employees values (100,null,20000,'jack8',sysdate -800);

1.普通分组

select employees_name, manager_id ,sum(salary) from employees where manager_id = 100 group by employees_name,manager_id;

2.rollup分组(它会根据某个列名再进行汇总统计)

select employees_name, manager_id ,sum(salary) from employees where manager_id = 100
 group by rollup(employees_name,manager_id);

3.cube分组

select employees_name, manager_id ,sum(salary) from employees where manager_id = 100
 group by cube(employees_name,manager_id);

4.grouping查询是否分组,显示 0 表示经过分组,显示 1 表示未经过分组

select employees_name, manager_id ,sum(salary),grouping(employees_name) gp_e_n,grouping(manager_id) gp_m_i 
from employees where manager_id = 100 group by cube(employees_name,manager_id);