开篇还是想吐槽一下这个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);