码疯窝

LeetCode 每日一题 — Department Highest Salary

2015/01/22 10:54:03    分类: 日志连载    0人评论 次浏览

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

分析: 此题出现了小插曲, 一度把output 和expect 看反了, 然后到处发帖子说leetcode的MYSQL engine有问题. output的结果是错的. 发完帖子才发现原来是我看反了, 赶紧删帖子去. =.=

SELECT d.Name AS Department, e.Name AS Employee, e.Salary AS Salary
FROM Employee e
INNER JOIN Department d
ON e.DepartmentId = d.Id
INNER JOIN (select max(Salary) Salary, DepartmentId from Employee group by DepartmentId) t
ON e.Salary = t.Salary
AND e.DepartmentId = t.DepartmentId;
继续查看有关 日志连载的文章

0个访客评论