185. 部门工资前三高的所有员工—join

最新推荐文章于 2023-07-02 12:48:51 发布

layla:)

最新推荐文章于 2023-07-02 12:48:51 发布

阅读量93

点赞数

分类专栏： # 数据库

本文链接：https://blog.csdn.net/ly000666/article/details/126686997

版权

SQL查询子查询部门薪资排名员工信息

关键词由CSDN通过智能技术生成

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

题目： 题目描述链接

解答：

select e1.name as 'Employee',e1.salary,d.name as 'Department'
from Employee e1 join Department d on d.id = e1.departmentId
where 3 > (
        select count(distinct e2.salary) from Employee e2 where e1.salary < e2.salary 
        and e1.departmentId = e2.departmentId
     )

讲解：
第一步： 先找出公司里前 3 高的薪水，意思是不超过三个值比这些值大

SELECT e1.Salary 
FROM Employee AS e1
WHERE 3 > 
		(SELECT  count(DISTINCT e2.Salary) 
		 FROM	Employee AS e2 
	 	 WHERE	e1.Salary < e2.Salary 	AND e1.DepartmentId = e2.DepartmentId) ;

举个栗子：
当 e1 = e2 = [4,5,6,7,8]

e1.Salary = 4，e2.Salary 可以取值 [5,6,7,8]，count(DISTINCT e2.Salary) = 4

e1.Salary = 5，e2.Salary 可以取值 [6,7,8]，count(DISTINCT e2.Salary) = 3

e1.Salary = 6，e2.Salary 可以取值 [7,8]，count(DISTINCT e2.Salary) = 2

e1.Salary = 7，e2.Salary 可以取值 [8]，count(DISTINCT e2.Salary) = 1

e1.Salary = 8，e2.Salary 可以取值 []，count(DISTINCT e2.Salary) = 0

最后 3 > count(DISTINCT e2.Salary)，所以 e1.Salary 可取值为 [6,7,8]，即集合前 3 高的薪水

第二步： 再把表 Department 和表 Employee 连接，获得各个部门工资前三高的员工。

SELECT
	Department.NAME AS Department,
	e1.NAME AS Employee,
	e1.Salary AS Salary 
FROM
	Employee AS e1,Department 
WHERE
	e1.DepartmentId = Department.Id 
	AND 3 > (SELECT  count( DISTINCT e2.Salary ) 
			 FROM	Employee AS e2 
			 WHERE	e1.Salary < e2.Salary 	AND e1.DepartmentId = e2.DepartmentId 	) 
ORDER BY Department.NAME,Salary DESC;