一、示例数据表
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
