join 、聚合、窗口简单练习 (使用 employee 员工表；employee_address 员工住址信息表； employee_connection 员工联系方式表； 表) -- 10. 求薪水在2000-4000元的员工信息(员工工号，职位，部门，电话，邮箱，街道，城市) -- 分别使用普通join和CTE两种语法实现 -- 11. 求按照城市分布的平均薪资和总薪资，并按照城市内的总薪资倒序排序，只取每个城市总薪资的top1 -- 12. 按照部门划分，求每个部门下员工所在城市数量最多的城市信息。

10. 使用普通join实现：

SELECT e.employee_id, e.job_title, e.department, ec.phone_number, ec.email_address, ea.street_address, ea.city
FROM employee e
INNER JOIN employee_address ea ON e.employee_id = ea.employee_id
INNER JOIN employee_connection ec ON e.employee_id = ec.employee_id
WHERE e.salary BETWEEN 2000 AND 4000;

使用CTE实现：

WITH cte_employee AS (
  SELECT employee_id, job_title, department, salary
  FROM employee
  WHERE salary BETWEEN 2000 AND 4000
),
cte_employee_address AS (
  SELECT employee_id, street_address, city
  FROM employee_address
),
cte_employee_connection AS (
  SELECT employee_id, phone_number, email_address
  FROM employee_connection
)
SELECT e.employee_id, e.job_title, e.department, ec.phone_number, ec.email_address, ea.street_address, ea.city
FROM cte_employee e
INNER JOIN cte_employee_address ea ON e.employee_id = ea.employee_id
INNER JOIN cte_employee_connection ec ON e.employee_id = ec.employee_id;

11.

SELECT ea.city, AVG(e.salary) AS avg_salary, SUM(e.salary) AS total_salary
FROM employee e
INNER JOIN employee_address ea ON e.employee_id = ea.employee_id
GROUP BY ea.city
ORDER BY total_salary DESC
LIMIT 1;

12.

WITH cte AS (
  SELECT e.department, ea.city, COUNT(DISTINCT ea.city) AS city_count
  FROM employee e
  INNER JOIN employee_address ea ON e.employee_id = ea.employee_id
  GROUP BY e.department, ea.city
),
cte2 AS (
  SELECT department, MAX(city_count) AS max_city_count
  FROM cte
  GROUP BY department
)
SELECT cte.department, cte.city
FROM cte
INNER JOIN cte2 ON cte.department = cte2.department AND cte.city_count = cte2.max_city_count;