一、示例數據表
1.1 假設我們有一張示例數據表“employees”，包含以下列：
id：每個員工的唯一標識符
name：員工姓名
gender：員工性別
salary：員工工資
department：員工所在部門
1.2 下面是創建employees表并插入樣本數據的MySQL腳本：
create TABLE employees( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, gender VARCHAR(10) NOT NULL, salary INT NOT NULL, department VARCHAR(50) NOT NULL);
insert INTO employees (name, gender, salary, department)VALUES ('Ramesh Gupta', 'Male', 55000, 'Sales'), ('Priya Sharma', 'Female', 65000, 'Marketing'), ('Sanjay Singh', 'Male', 75000, 'Sales'), ('Anjali Verma', 'Female', 45000, 'Finance'), ('Rajesh Sharma', 'Male', 80000, 'Marketing'), ('Smita Patel', 'Female', 60000, 'HR'), ('Vikram Yadav', 'Male', 90000, 'Sales'), ('Neha Sharma', 'Female', 55000, 'Marketing'), ('Rahul Singh', 'Male', 70000, 'Finance'), ('Sonali Gupta', 'Female', 50000, 'Sales');
Employees 表
二、查詢
2.1 查詢每個部門中男女職工的平均薪水
實現方案：
select department, gender, AVG(salary) AS avg_salary
from employees
GROUP BY department, gender;
輸出：
2.2 查詢每個部門中薪水最高的員工姓名及薪水
實現方案：
select name, salary
from employees
where (department, salary) IN (select department, MAX(salary)
from employees
GROUP BY department);
輸出：
2.3 查詢每個部門中比所在部門平均薪水高的員工信息
實現方案：
select name, salary, department
from employeeswhere salary > (select AVG(salary)
from employees AS e2
where e2.department = employees.department);
輸出：
2.4 查詢每個部門中薪水前三名的員工信息
實現方案：
select e.department, e.name, e.salary
from employees e
where (select COUNT(*)
from employees
where department = e.department
AND salary > e.salary) < 3;
輸出：
2.5 查詢每個部門中比所在部門平均薪水高的員工姓名（該問題與第三個問題一樣，只不過這里使用連接實現）
實現方案：
select e.namefrom employees e
JOIN (select department, AVG(salary) AS avg_salary
from employees
GROUP BY department) AS dept_avg
ON e.department = dept_avg.department
where e.salary > dept_avg.avg_salary;
輸出：
2.6 查詢每個部門中最高薪水的員工信息
實現方案：
WITH max_salary AS (select department, MAX(salary) AS highest_salary
from employees
GROUP BY department)
select m.department, e.name, e.salaryfrom employees e
JOIN max_salary m
ON e.department = m.department AND e.salary = m.highest_salary;
輸出：
該實現需要MySQL 8.0及以上版本才可運行，因為WITH...AS...子句只有8.0及以上版本才支持。
該文章在 2023/11/16 23:02:23 編輯過
400 186 1886
