1、查询每个部门、职位的总工资,以及每个部门的总工资,整个公司的总工资。
select department_id,job_id,sum(salary)
from employees
group by rollup(department_id,job_id);
2、查询每个部门、职位的总工资,以及每个部门的总工资,整个公司的总工资。
select department_id,job_id,sum(salary)
from employees
group by cube(department_id,job_id);
3、使用grouping函数,在合适的地方写上部门小计,职位小计和总计。
select
case grouping(department_id)
when 1 then 'all department'
else to_char(department_id)
end as department,
case grouping(job_id)
when 1 then 'all jobs'
else job_id
end as job,
sum(salary)
from employees
group by rollup(department_id,job_id)
order by department_id,job_id;
4、使用层次查询和level伪列查询公司管理层次图。
select level,employee_id,manager_id,last_name,job_id from employees
start with job_id = (select job_id from employees where employee_id=108)
connect by prior manager_id=employee_id;
5、使用层次查询和level伪列查询某个领导的直接和间接员工。
select level,employee_id,manager_id,last_name,job_id from employees
start with job_id=(select job_id from employees where employee_id=108)
connect by prior employee_id=manager_id;