sql之内连结与关联子查询

最新推荐文章于 2023-11-17 16:31:35 发布

hxchuadian

最新推荐文章于 2023-11-17 16:31:35 发布

阅读量533

点赞数

文章标签： sql 数据库 database

本文链接：https://blog.csdn.net/hxchuadian/article/details/121595455

版权

该博客展示了如何在SQL中创建`employee`和`Department`表，并进行数据插入。接着，它演示了如何使用内连接查询员工及其所属部门信息，以及找出每个部门薪水最高的员工。这些SQL操作对于理解和应用数据库查询至关重要。

摘要由CSDN通过智能技术生成

创建employee表及Department表

USE shop;
CREATE TABLE employee
(Id integer(2),
Name varchar(10),
Salary varchar(10),
DepartmentId integer(2)
);

USE shop;
CREATE TABLE Department
(Id integer(2),
Name varchar(10)
);

插入数据

USE shop;
INSERT INTO employee VALUES ('1','Joe','70000','1'),
('2','Henry','80000','2'),
('3','Sam','60000','2'),
('4','Max','90000','1');

USE shop;
INSERT INTO Department VALUES ('1','IT'),
('2','Sales');

使用内连结

USE shop;
SELECT dp.Name AS Departmrnt,
ep.Name AS employee,
ep.Salary
FROM employee AS ep
INNER JOIN Department AS dp
ON ep.DepartmentId = dp.id;

USE shop;
SELECT dp.Name AS Departmrnt,
ep.Name AS employee,
ep.Salary
FROM Department AS dp
INNER JOIN
(SELECT Id,Name,Salary,DepartmentId
FROM employee AS ep1
WHERE Salary =
(SELECT MAX(Salary)
FROM employee AS ep2
WHERE ep1.DepartmentId = ep2.DepartmentId
GROUP BY DepartmentId)
) AS ep
ON ep.DepartmentId = dp.id;

效果如下：