SQL案例 - 统计员工薪资扣除比例

描述

某公司员工信息数据及员工薪资信息数据如下：

员工信息表 staff_tb（staff_id-员工id，staff_name-员工姓名，staff_gender-员工性别，post-员工岗位类别，department-员工所在部门），如下所示：

staff_id	staff_name	staff_gender	post	department
1	Angus	male	Financial	dep1
2	Cathy	female	Director	dep1
3	Aldis	female	Director	dep2
4	Lawson	male	Engineer	dep1
5	Carl	male	Engineer	dep2
6	Ben	male	Engineer	dep1
7	Rose	female	Financial	dep2

员工薪资信息表 salary_tb（salary_id-薪资信息id，staff_id-员工id，normal_salary-标准薪资，dock_salary-扣除薪资），如下所示：

salary_id	staff_id	normal_salary	dock_salary
10	1	12000	2500
11	2	11000	2200
12	3	9000	1800
13	4	10500	1900
14	5	13500	2100
15	6	7500	1000
16	7	50000	5000

问题：请统计部门 dep1 扣除薪资比例。

要求输出：员工id、姓名、扣除薪资占比（保留1位小数以百分数形式输出）按照扣除薪资占比降序排序

知识点：关联查询、子查询、聚合函数、字符串函数
示例数据结果如下：

staff_id	staff_name	dock_ratio
1	Angus	20.8%
2	Cathy	20.0%
4	Lawson	18.1%
6	Ben	13.3%

解释：部门dep1共有员工1、2、4、6

其中员工1标准薪资为12000，扣除薪资为2500，故扣除薪资比例为2500/12000=20.8%

其他结果同理。

示例1

输入：

drop table if exists  `staff_tb` ; 
CREATE TABLE `staff_tb` (
`staff_id` int(11) NOT NULL,
`staff_name` varchar(16) NOT NULL,
`staff_gender` char(8) NOT NULL,
`post` varchar(11) NOT NULL,
`department` varchar(16) NOT NULL,
PRIMARY KEY (`staff_id`));
INSERT INTO staff_tb VALUES(1,'Angus','male','Financial','dep1'); 
INSERT INTO staff_tb VALUES(2,'Cathy','female','Director','dep1'); 
INSERT INTO staff_tb VALUES(3,'Aldis','female','Director','dep2'); 
INSERT INTO staff_tb VALUES(4,'Lawson','male','Engineer','dep1'); 
INSERT INTO staff_tb VALUES(5,'Carl','male','Engineer','dep2'); 
INSERT INTO staff_tb VALUES(6,'Ben','male','Engineer','dep1'); 
INSERT INTO staff_tb VALUES(7,'Rose','female','Financial','dep2'); 

drop table if exists  `salary_tb` ; 
CREATE TABLE `salary_tb` (
`salary_id` int(11) NOT NULL,
`staff_id` int(11) NOT NULL,
`normal_salary` int(11) NOT NULL,
`dock_salary` int(11) NOT NULL,
PRIMARY KEY (`salary_id`));
INSERT INTO salary_tb VALUES(10,1,12000,2500); 
INSERT INTO salary_tb VALUES(11,2,11000,2200); 
INSERT INTO salary_tb VALUES(12,3,9000,1800); 
INSERT INTO salary_tb VALUES(13,4,10500,1900); 
INSERT INTO salary_tb VALUES(14,5,13500,2100); 
INSERT INTO salary_tb VALUES(15,6,7500,1000); 
INSERT INTO salary_tb VALUES(16,7,50000,5000);

输出：

staff_id|staff_name|dock_ratio
1|Angus|20.8%
2|Cathy|20.0%
4|Lawson|18.1%
6|Ben|13.3%

答案

解法1：

select 
    e.staff_id,
    e.staff_name,
    concat(
        round(s.dock_salary/s.normal_salary*100,1),
        '%'
    ) dock_ratio
from
    staff_tb e
    left join salary_tb s on e.staff_id=s.staff_id
where
    e.department='dep1'
order by dock_ratio desc;

*本案例来自牛客网，但答案为原创，如有雷同纯属巧合*