求总和的百分比

最新推荐文章于 2019-06-02 23:04:34 发布

转载最新推荐文章于 2019-06-02 23:04:34 发布 · 320 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/minisculestep/p/4895924.html

本文介绍如何使用SQL语句实现部门员工工资的分组汇总，并通过SUM()和OVER()函数计算各组工资占总工资的比例，同时提供了一个简化的方法使用RATIO_TO_REPORT函数直接计算工资比例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

创建测试用表：

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);

转载于:https://www.cnblogs.com/minisculestep/p/4895924.html