题目
给如下两个表，写一个查询语句，求出在每一个工资发放日，每个部门的平均工资与公司的平均工资的比较结果 （高 / 低 / 相同）。
表： salary
| id | employee_id | amount | pay_date |
|---|---|---|---|
| 1 | 1 | 9000 | 2017-03-31 |
| 2 | 2 | 6000 | 2017-03-31 |
| 3 | 3 | 10000 | 2017-03-31 |
| 4 | 1 | 7000 | 2017-02-28 |
| 5 | 2 | 6000 | 2017-02-28 |
| 6 | 3 | 8000 | 2017-02-28 |
employee_id 字段是表 employee 中 employee_id 字段的外键。
| employee_id | department_id |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
对于如上样例数据，结果为：
| pay_month | department_id | comparison |
|---|---|---|
| 2017-03 | 1 | higher |
| 2017-03 | 2 | lower |
| 2017-02 | 1 | same |
| 2017-02 | 2 | same |
解释
在三月，公司的平均工资是 (9000+6000+10000)/3 = 8333.33...
由于部门 '1' 里只有一个 employee_id 为 '1' 的员工，所以部门 '1' 的平均工资就是此人的工资 9000 。因为 9000 > 8333.33 ，所以比较结果是 'higher'。
第二个部门的平均工资为 employee_id 为 '2' 和 '3' 两个人的平均工资，为 (6000+10000)/2=8000 。因为 8000 < 8333.33 ，所以比较结果是 'lower' 。
在二月用同样的公式求平均工资并比较，比较结果为 'same' ，因为部门 '1' 和部门 '2' 的平均工资与公司的平均工资相同，都是 7000 。
生成数据
DROP TABLE salary
CREATE TABLE salary(Id INT,employee_id INT,amount INT,pay_date DATE);
INSERT INTO salary VALUES(1,1,9000,'2017-03-31');
INSERT INTO salary VALUES(2,2,6000,'2017-03-31');
INSERT INTO salary VALUES(3,3,10000,'2017-03-31');
INSERT INTO salary VALUES(4,1,7000,'2017-02-28');
INSERT INTO salary VALUES(5,2,6000,'2017-02-28');
INSERT INTO salary VALUES(6,3,8000,'2017-02-28');
DROP TABLE employee6
CREATE TABLE employee6(employee_id INT,department_id INT);
INSERT INTO employee6 VALUES(1,1);
INSERT INTO employee6 VALUES(2,2);
INSERT INTO employee6 VALUES(3,2);
解答
pay_month 需要把年和月提取出来
SELECT S.`Id`, S.`employee_id`, S.`amount`, CONCAT(YEAR(S.`pay_date`), '-', MONTH(S.`pay_date`) ) AS pay_month
FROM salary AS S;
好像内置函数也是可以实现的 date_formate()
SELECT S.`Id`, S.`employee_id`, S.`amount`, DATE_FORMAT(S.`pay_date`, '%Y-%m') AS pay_month
FROM salary AS S;
对每一个pay_month求平均工资
SELECT tmp.pay_month, AVG(tmp.amount) AS avg_salary
FROM (SELECT S.`Id`, S.`employee_id`, S.`amount`, DATE_FORMAT(S.`pay_date`, '%Y-%m') AS pay_month
FROM salary AS S) AS tmp
GROUP BY tmp.pay_month ;
两表连接，然后按年月和部门分组计算平均工资
SELECT tmp.pay_month, E.`department_id`, AVG(tmp.amount) AS avg_dep_salary
FROM (SELECT S.`Id`, S.`employee_id`, S.`amount`, DATE_FORMAT(S.`pay_date`, '%Y-%m') AS pay_month
FROM salary AS S) AS tmp
JOIN employee6 AS E
ON E.`employee_id` = tmp.employee_id
GROUP BY tmp.pay_month, E.`department_id`;
按月份两表连接
SELECT *
FROM (SELECT tmp.pay_month, AVG(tmp.amount) AS avg_salary
FROM (SELECT S.`Id`, S.`employee_id`, S.`amount`, DATE_FORMAT(S.`pay_date`, '%Y-%m') AS pay_month
FROM salary AS S) AS tmp
GROUP BY tmp.pay_month) AS A
JOIN (SELECT tmp.pay_month, E.`department_id`, AVG(tmp.amount) AS avg_dep_salary
FROM (SELECT S.`Id`, S.`employee_id`, S.`amount`, DATE_FORMAT(S.`pay_date`, '%Y-%m') AS pay_month
FROM salary AS S) AS tmp
JOIN employee6 AS E
ON E.`employee_id` = tmp.employee_id
GROUP BY tmp.pay_month, E.`department_id`) AS B
ON A.pay_month = B.pay_month
然后比较两个平均工资即可
SELECT A.pay_month, B.department_id,
IF(B.avg_dep_salary > A.avg_salary, 'higher', IF(B.avg_dep_salary < A.avg_salary, 'lower', 'same')) AS comparison
FROM (SELECT tmp.pay_month, AVG(tmp.amount) AS avg_salary
FROM (SELECT S.`Id`, S.`employee_id`, S.`amount`, DATE_FORMAT(S.`pay_date`, '%Y-%m') AS pay_month
FROM salary AS S) AS tmp
GROUP BY tmp.pay_month) AS A
JOIN (SELECT tmp.pay_month, E.`department_id`, AVG(tmp.amount) AS avg_dep_salary
FROM (SELECT S.`Id`, S.`employee_id`, S.`amount`, DATE_FORMAT(S.`pay_date`, '%Y-%m') AS pay_month
FROM salary AS S) AS tmp
JOIN employee6 AS E
ON E.`employee_id` = tmp.employee_id
GROUP BY tmp.pay_month, E.`department_id`) AS B
ON A.pay_month = B.pay_month;
用case when 的逻辑也可
SELECT A.pay_month, B.department_id,
CASE
WHEN B.avg_dep_salary > A.avg_salary THEN 'higher'
WHEN B.avg_dep_salary < A.avg_salary THEN 'lower'
ELSE 'same'
END AS `comparison`
FROM (SELECT tmp.pay_month, AVG(tmp.amount) AS avg_salary
FROM (SELECT S.`Id`, S.`employee_id`, S.`amount`, DATE_FORMAT(S.`pay_date`, '%Y-%m') AS pay_month
FROM salary AS S) AS tmp
GROUP BY tmp.pay_month) AS A
JOIN (SELECT tmp.pay_month, E.`department_id`, AVG(tmp.amount) AS avg_dep_salary
FROM (SELECT S.`Id`, S.`employee_id`, S.`amount`, DATE_FORMAT(S.`pay_date`, '%Y-%m') AS pay_month
FROM salary AS S) AS tmp
JOIN employee6 AS E
ON E.`employee_id` = tmp.employee_id
GROUP BY tmp.pay_month, E.`department_id`) AS B
ON A.pay_month = B.pay_month;
网友评论