更多关于LeetCode习题总结请翻阅我的博客:
LeetCode - mysql子查询【184. 部门工资最高的员工】
子查询
一、按查询结果分类:
1.作为数据源使用;
2.作为选择条件使用;
二、按常见的子查询分类:
1.由比较运算符引发的子查询;
2.由 [NOT] IN 引发的子查询: LeetCode题184部门工资最高的员工
3.INSERT ...SELECT....:插入检索的数据
题184. 部门工资最高的员工
思路:1.先在employee表中,分组查找出每个部门分数最高的员工。2.再与department表内链接
1.分组查找出每个部门分数最高的员工
SELECT e2.DepartmentId,MAX(e2.Salary) AS Salary FROM Employee e2
GROUP BY e2.DepartmentId
输出结果为:
我们若再添加Name列,可以发现:工资最高的员工并非是一一对应的,而是会取分组前的第一行。
即若想通过Group By求最大值的其他信息,这样子的方式是不允许的。
SELECT e2.DepartmentId,e2.Name,MAX(e2.Salary) AS Salary FROM Employee e2
GROUP BY e2.DepartmentId
2. employee表与department表内链接:
SELECT d.Name AS Department,e1.Name AS Employee,e1.Salary
FROM Employee e1 INNER JOIN Department d
ON e1.DepartmentId = d.Id
3.将上述代码通过子查询合并
SELECT d.Name AS Department,e1.Name AS Employee,e1.Salary
FROM Employee e1 INNER JOIN Department d
ON e1.DepartmentId = d.Id
WHERE (e1.DepartmentId,e1.Salary)
IN (SELECT e2.DepartmentId,MAX(e2.Salary) AS Salary FROM Employee e2
GROUP BY e2.DepartmentId );