一. 題目： 給如下兩個表，寫一個查詢語句，求出在每一個工資發放日，每個部門的平均工資與公司的平均工資的比較結果 （高 / 低 / 相同）。 工資表：salary | id | employee_id | amount | pay_date | | | | | | | 1 | 1 | 9000 | ...
一. 題目
給如下兩個表，寫一個查詢語句，求出在每一個工資發放日，每個部門的平均工資與公司的平均工資的比較結果 （高 / 低 / 相同）。
工資表：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
| employee_id | department_id |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
表salary 中employee_id 欄位是表 employee 中 employee_id 欄位的外鍵。
對於如上樣例數據，結果為：
| 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=800。因為 8000 < 8333.33 ，所以比較結果‘lower’。
-
在二月用同樣的公式求平均工資並比較，比較結果為‘same’，因為部門‘1’和部門‘2’的平均工資與公司的平均工資相同，都是 7000。
二. 答案
SELECT t1.發工資月份, t1.部門,
CASE
WHEN t1.部門平均工資 > t2.公司平均工資 THEN 'higher'
WHEN t1.部門平均工資 < t2.公司平均工資 THEN 'lower'
ELSE 'same'
END AS '比較結果',
t1.部門平均工資,
t2.公司平均工資
FROM
(
-- 拿到每個月份部門的平均工資
SELECT employee.department_id AS '部門', SUM(salary.amount) / COUNT(salary.employee_id) '部門平均工資', DATE_FORMAT(salary.pay_date, '%Y-%m') AS '發工資月份' FROM salary
RIGHT JOIN employee ON salary.employee_id = employee.employee_id
GROUP BY 發工資月份, employee.department_id
) t1
INNER JOIN
(
-- 拿到每個月份公司的平均工資
SELECT DATE_FORMAT(salary.pay_date, '%Y-%m') AS '發工資月份', SUM(salary.amount) / COUNT(salary.employee_id) AS '公司平均工資'
FROM salary
GROUP BY 發工資月份
) t2
ON t1.發工資月份 = t2.發工資月份
本文來自博客園，作者：Schieber，轉載請註明原文鏈接：https://www.cnblogs.com/xiqingbo/p/arithmetic-02.html
