MySQL 数据库操作实践：两表联查之分组成绩第几问题

在本篇博客中，我将展示MySQL 从创建表、到插入数据，并进行一些复杂的查询操作。

1. 建立表格

首先，我们创建两个表：department（部门）和 employee（员工）。

CREATE TABLE department (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2)
);

2.插入数据

接下来，让我们向这两个表中插入一些数据：

-- 部门表数据插入
INSERT INTO department (id, name) VALUES
(1, 'HR'),
(2, 'Finance');

-- 员工表数据插入
INSERT INTO employee (id, name, department_id, salary) VALUES
(1, 'Amy', 1, 8000.00),
(2, 'Ben', 2, 9000.00),
(3, 'Charlie', 1, 8500.00),
(4, 'Diana', 2, 8200.00),
(5, 'Eric', 1, 8300.00),
(6, 'Fiona', 2, 8700.00);

3.查询问题

问题1：查询每个部门中工资最高的员工姓名及其工资

SELECT d.name AS department_name, e.name AS employee_name, e.salary AS max_salary
FROM department d
JOIN employee e ON d.id = e.department_id
WHERE (e.department_id, e.salary) IN 
    (SELECT department_id, MAX(salary) FROM employee GROUP BY department_id);

SQL解释：
子查询：(SELECT department_id, MAX(salary) FROM employee GROUP BY department_id)
这个子查询用于找出每个部门中工资最高的员工的工资。

主查询中的关联和过滤条件：SELECT d.name AS department_name, e.name AS employee_name, e.salary AS max_salary
通过将主查询中员工所在部门和工资与子查询的结果进行比对，找到符合条件的员工，并返回他们的部门名称、姓名和最高工资。

这样的查询结构使我们能够有效地找到每个部门中工资最高的员工，并展示他们的相关信息。
问题2：查询每个部门中工资排名第2高的员工姓名及其工资

SELECT d.name AS department_name, e.name AS employee_name, e.salary AS second_highest_salary
FROM department d
JOIN employee e ON d.id = e.department_id
WHERE (e.department_id, e.salary) IN 
    (SELECT department_id, MAX(salary) FROM employee WHERE salary < 
        (SELECT MAX(salary) FROM employee e2 WHERE e.department_id = e2.department_id) GROUP BY department_id);

SQL解释：
子查询1：SELECT MAX(salary) FROM employee e2 WHERE e.department_id = e2.department_id

这个子查询用于找出每个部门中工资最高的员工的工资。

子查询2：SELECT department_id, MAX(salary) FROM employee WHERE salary < (Subquery1) GROUP BY department_id
这个子查询根据第一个子查询的结果，找出每个部门中工资排名第二高的员工的工资。它首先去除了最高工资，然后再取出次高的工资。

主查询中过滤条件：WHERE (e.department_id, e.salary) IN (Subquery2)
通过将主查询中员工所在部门和工资与第二个子查询的结果进行比对，找到符合条件的员工，并返回他们的部门名称、姓名和次高工资。
这样的嵌套查询结构使得我们能够在不引入额外的复杂逻辑的情况下，完成对每个部门中工资排名第二高的员工的查询。
问题3：查询每个部门中工资排名第3高的员工姓名及其工资

SELECT d.name AS department_name, e.name AS employee_name, e.salary AS third_highest_salary
FROM department d
JOIN employee e ON d.id = e.department_id
WHERE (e.department_id, e.salary) IN 
    (SELECT department_id, MAX(salary) FROM employee WHERE salary < 
        (SELECT MAX(salary) FROM employee e2 WHERE e.department_id = e2.department_id) AND salary NOT IN 
            (SELECT MAX(salary) FROM employee e3 WHERE e.department_id = e3.department_id) GROUP BY department_id);

SQL解释及修改说明：
子查询2修改：
在原来的第二个子查询中，增加了一个 AND salary NOT IN 条件，以排除掉最高和次高的工资，从而获取到第三高的工资。

主查询中过滤条件：
通过将主查询中员工所在部门和工资与修改后的第二个子查询的结果进行比对，找到符合条件的员工，并返回他们的部门名称、姓名和第三高工资。

这样修改后的查询语句应该能够准确地查询出每个部门中工资排名第3高的员工姓名及其工资。

问题4：查询每个部门中工资排名第4高的员工姓名及其工资

SELECT d.name AS department_name, e.name AS employee_name, e.salary AS fourth_highest_salary
FROM department d
JOIN employee e ON d.id = e.department_id
WHERE (e.department_id, e.salary) IN 
    (SELECT department_id, MAX(salary) FROM employee WHERE salary < 
        (SELECT MAX(salary) FROM employee e2 WHERE e.department_id = e2.department_id) AND salary NOT IN 
            (SELECT MAX(salary) FROM employee e3 WHERE e.department_id = e3.department_id) AND salary NOT IN 
                (SELECT MAX(salary) FROM employee e4 WHERE e.department_id = e4.department_id) GROUP BY department_id);

SQL解释及修改说明：
子查询2修改：
在原来的第二个子查询中，增加了一个 AND salary NOT IN 条件，以排除掉最高、次高和第三高的工资，从而获取到第四高的工资。

相信通过以上步骤，你已经感受到了类似题目中的规律，希望对你有所帮助！