Oracle求部门员工工资占总工资的比率

--根据每个部门来统计部门工资总和

select deptid, sum(sal) 工资合计 from emp group by deptid;

--根据每个部门来统计部门工资总和
select deptid, 工资合计, sum(工资合计) over() as 总合计
  from (select deptid, sum(sal) 工资合计 from emp group by deptid) x;
 
 
 
 select
    deptid  部门,
        工资合计,
        总合计,
        round((工资合计/总合计) * 100 , 2) || ‘%‘ as 工资比例
   from (select deptid,
                工资合计,
                sum(工资合计) over() as 总合计 from (select deptid,
                                                     sum(sal) 工资合计
                                                from emp
                                               group by deptid) x ) y
  order by 1;
 

  --round(number,2) 保留下面两位小数  根据截取后一位小数来进行四舍五入
   select round(2342.54665,1) from dual;  


--使用专用的比例函数
select deptid,
       工资合计,
       sum(工资合计) over() as 总合计,
       round(ratio_to_report(工资合计) over() * 100, 2) || ‘%‘ as 工资比例
  from (select deptid, sum(sal) 工资合计 from emp group by deptid)
 order by 1 desc;


--使用分析函数 查询每个员工在对应部门中所占的工资比列
select deptid,
       ename,
       sal,
       round(ratio_to_report(sal) over(partition by deptid) * 100, 2) || ‘%‘ 工资比例
  from emp
 order by 1, 2;

Oracle求部门员工工资占总工资的比率

标签：