• 求总和的百分比


    现已知各部门的员工工资,要求计算各部门工资合计,以及合计工资占总工资的比例。

    创建测试用表:

    CREATE OR REPLACE VIEW v AS
    SELECT '10' AS depno, '101' AS empno, 'A' AS ename, '2000' AS sal FROM DUAL
    UNION ALL
    SELECT '10' AS depno, '102' AS empno, 'B' AS ename, '1000' AS sal FROM DUAL
    UNION ALL
    SELECT '10' AS depno, '103' AS empno, 'C' AS ename, '2500' AS sal FROM DUAL
    UNION ALL
    SELECT '20' AS depno, '104' AS empno, 'D' AS ename, '2000' AS sal FROM DUAL
    UNION ALL
    SELECT '20' AS depno, '105' AS empno, 'E' AS ename, '4000' AS sal FROM DUAL
    UNION ALL
    SELECT '20' AS depno, '106' AS empno, 'F' AS ename, '4000' AS sal FROM DUAL;
    
    SELECT * FROM v;
    

    其中的工资合计很简单,直接用 group by 语句就可以得到,要点在于总工资的合计,需要用到分析函数:sum()和over()。

    当over()后不加任何内容时,就是对所有数据进行汇总,步骤如下。

     

    1.分组汇总

    SELECT depno, SUM(sal) 工资合计 FROM v GROUP BY depno;
    

    2.通过分析函数获取总合计

    SELECT depno, 工资合计, SUM(工资合计) OVER() AS 总合计
      FROM (SELECT depno, SUM(sal) 工资合计 FROM v GROUP BY depno);
    

    3.得到总合计后就可以计算比例

    SELECT depno AS 部门,
           工资合计,
           总合计,
           ROUND((工资合计 / 总合计) * 100, 2) AS 工资比例
      FROM (SELECT depno, 工资合计, SUM(工资合计) OVER() AS 总合计
              FROM (SELECT depno, SUM(sal) 工资合计 FROM v GROUP BY depno));
    

    另外,我们也可以用专用的比例函数“ratio_to_report”来直接计算:

    SELECT depno AS 部门, ROUND(RATIO_TO_REPORT(工资合计) OVER() * 100, 2) AS 工资比例
      FROM (SELECT depno, SUM(sal) 工资合计 FROM v GROUP BY depno);
    
  • 相关阅读:
    PDF文档生成缩略图
    zTree数据回显
    window.showModalDialog基础
    Java获取两个时间段内的所有日期
    CSS设置超出表格的内容用省略号显示
    Ajax提交form表单
    普通java类在Tomcat启动时获取ServletContext
    mysql中sql优化和合理使用索引
    mysql数据类型详解系列
    如何干净的清除slave同步信息
  • 原文地址:https://www.cnblogs.com/minisculestep/p/4895924.html
Copyright © 2020-2023  润新知