SQL实现LeetCode(184.系里最高薪水)(2)-木庄网络博客

解法一：

SELECT d.Name AS Department, e1.Name AS Employee, e1.Salary FROM Employee e1
JOIN Department d ON e1.DepartmentId = d.Id WHERE Salary IN 
(SELECT MAX(Salary) FROM Employee e2 WHERE e1.DepartmentId = e2.DepartmentId);

我们也可以不用Join关键字，直接用Where将两表连起来，然后找最高薪水的方法和上面相同：

解法二：

SELECT d.Name AS Department, e.Name AS Employee, e.Salary FROM Employee e, Department d
WHERE e.DepartmentId = d.Id AND e.Salary = (SELECT MAX(Salary) FROM Employee e2 WHERE e2.DepartmentId = d.Id);

下面这种方法没用用到Max关键字，而是用了>=符号，实现的效果跟Max关键字相同，参见代码如下：

解法三：

SELECT d.Name AS Department, e.Name AS Employee, e.Salary FROM Employee e, Department d
WHERE e.DepartmentId = d.Id AND e.Salary >= ALL (SELECT Salary FROM Employee e2 WHERE e2.DepartmentId = d.Id);

类似题目：

Second Highest Salary

Combine Two Tables

到此这篇关于SQL实现LeetCode(184.系里最高薪水)的文章就介绍到这了,更多相关SQL实现系里最高薪水内容请搜索

标签：SQL

返回前面的内容

相关阅读 >>

mysql5.7.14下载安装图文教程及mysql数据库语句入门大全

sql server数据库怎么安装？

sql server是免费的吗?

数据库触发器db2和sqlserver有哪些区别

mysql 基础常用命令总结