Oracle中和计算总⼯资,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
oracle四舍五入order by 1, 2;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。