615. Average Salary: Departments VS Company----通过DATE_FORMAT( , “%Y-%m“)获得年和月

本文链接：https://blog.csdn.net/betty1121/article/details/108414623

Given two tables as below, write a query to display the comparison result (higher/lower/same) of the average salary of employees in a department to the company's average salary.

Table: 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 |

The employee_id column refers to the employee_id in the following table employee.

| employee_id | department_id |
|-------------|---------------|
| 1           | 1             |
| 2           | 2             |
| 3           | 2             |

So for the sample data above, the result is:

| pay_month | department_id | comparison  |
|-----------|---------------|-------------|
| 2017-03   | 1             | higher      |
| 2017-03   | 2             | lower       |
| 2017-02   | 1             | same        |
| 2017-02   | 2             | same        |

Explanation

In March, the company's average salary is (9000+6000+10000)/3 = 8333.33...

The average salary for department '1' is 9000, which is the salary of employee_id '1' since there is only one employee in this department. So the comparison result is 'higher' since 9000 > 8333.33 obviously.

The average salary of department '2' is (6000 + 10000)/2 = 8000, which is the average of employee_id '2' and '3'. So the comparison result is 'lower' since 8000 < 8333.33.

With he same formula for the average salary comparison in February, the result is 'same' since both the department '1' and '2' have the same average salary with the company, which is 7000.

WITH t1 AS (SELECT e.department_id, pay_date, AVG(amount) avg_ds
FROM salary s, employee e
WHERE s.employee_id = e.employee_id
GROUP BY e.department_id, pay_date),
t2 AS (SELECT pay_date, AVG(amount) avg_cs
FROM salary s
GROUP BY pay_date)
SELECT DATE_FORMAT(t1.pay_date, "%Y-%m") AS pay_month, t1.department_id, 
CASE 
  WHEN avg_ds > avg_cs THEN 'higher'
  WHEN avg_ds < avg_cs THEN 'lower'
  ELSE 'same' 
END AS comparison
  FROM t1, t2
  WHERE t1.pay_date = t2.pay_date;

如果是年月比较，如要判断date 是否是2020年6月，可以写成date> “2020-06” and date< “2020-07”，但不能写成 date> “2020-06-01”，系统会判断date不满足条件。

https://www.w3schools.com/sql/func_mysql_date_format.asp