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

原文链接：
https://www.cnblogs.com/laotan/p/4540677.html

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

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;

SELECT
    t.DEPT_NO,
    t.DEPT_NAME,
    COUNT( * ) AS COUNTS,
    sum( COUNT( * ) ) over ( ) AS "total",
    round( ratio_to_report ( COUNT( * ) ) over ( ) * 100, 2 ) || '%' AS "proportion" 
FROM
    T_DEPARTMENT t 
GROUP BY
    t.DEPT_NO,
    t.DEPT_NAME 
ORDER BY
    t.DEPT_NO

SELECT
    a.DEPT_NO AS "deptNo",
    a.DEPT_NAME AS "deptName",
    a.COUNTS AS "counts",
    sum( a.COUNTS ) over ( ) AS "total",
    round( ratio_to_report ( a.COUNTS ) over ( ) * 100, 2 ) || '%' AS "proportion" 
FROM
    (
    SELECT
        t.DEPT_NO,
        t.DEPT_NAME,
        COUNT( * ) AS COUNTS 
    FROM
        T_DEPARTMENT t 
    GROUP BY
        t.DEPT_NO,
        t.DEPT_NAME 
    ORDER BY
    t.DEPT_NO 
    ) a