最近公司有这样一个需求:如果有10万奖金,要分摊给员工,按每个人的实际能力分摊,要求不能有剩余。看到网上并没有相关的帖子,于是借此记录下我的解题思路。
一开始我的做法是用10w*(每个人的工资/全部人工资之和),计算出每个人应得的金额,round函数用来精确小数点后几位
SELECT e_id '员工id',e_salary '薪资',ROUND((100000*e_salary/(SELECT SUM(e_salary) FROM employee)),2) '奖金' FROM employee
图1 数据库设计
|
图2 错误查询结果
|
但这个分摊如果有除不尽的情况就会使分得的奖金加起来总和不等于10w(上图中总和为100000.01),特别是每个员工薪资相同时会出现更明显的除不尽问题
图3 相同薪资出现的除不尽情况
|
|
这时候我想起了小学时学过的知识:最后一个人的奖金应该等于10w减去其他人的奖金之和,奖金总和是sum(奖金)我们都知道,但sql语句又该怎么写?直接加上sum(奖金)是万万行不通的,就当我黔驴技穷的时候,union all 帮我解决了这一问题!
稍微理下思路:
1. 计算每个人奖金的时候应该先把最后一个员工去掉,把查询结果当做表1
2. 通过10w-sum(奖金)就得到了最后一个员工应得的金额,然后查出他的其他字段作为表2
3. 将表1和表2通过union all关键字纵向合并成一张表
最后的sql语句应该是这样的:
SELECT e_id '员工id',e_salary '薪资',ROUND((100000*e_salary/(select sum(e_salary) from employee)),2) '奖金'
FROM employee
WHERE e_id != (SELECT MAX(e_id) FROM employee LIMIT 1)
UNION ALL
SELECT e_id '员工id',e_salary '薪资',100000-(SELECT SUM(t1.奖金)
FROM
(SELECT e_id '员工id',e_salary '薪资',ROUND((100000*e_salary/(SELECT SUM(e_salary) FROM employee)),2) '奖金' FROM employee WHERE e_id != (SELECT MAX(e_id) FROM employee LIMIT 1)) t1) '奖金'
FROM employee
WHERE e_id = (SELECT MAX(e_id) FROM employee LIMIT 1)
图4 最终查询结果
|
|