学习php网站开发,微信开发小程序教程,北京软件app开发,网页设计技巧MySQL 数据库操作实践#xff1a;两表联查之分组成绩第几问题
在本篇博客中#xff0c;我将展示MySQL 从创建表、到插入数据#xff0c;并进行一些复杂的查询操作。
1. 建立表格
首先#xff0c;我们创建两个表#xff1a;department#xff08;部门#xff09;和 em…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解释 子查询1SELECT MAX(salary) FROM employee e2 WHERE e.department_id e2.department_id
这个子查询用于找出每个部门中工资最高的员工的工资。
子查询2SELECT 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 条件以排除掉最高、次高和第三高的工资从而获取到第四高的工资。 相信通过以上步骤你已经感受到了类似题目中的规律希望对你有所帮助
