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

返回前面的内容

相关阅读 >>

mysql 案例分析讲解外连接语法

通过格式良好的sql提高效率和准确性

sql server 2008完全卸载方法(其他版本类似)第22页

sqlserver将数据库中的表复制到另一个数据库

sql server 交集,差集的用法详解