Leetcode: Department Highest Salary
来源:互联网 发布:天刀客户端优化 编辑:程序博客网 时间:2024/06/07 00:33
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 |
+————+———-+——–+
Answer:
Note that there can be more than one person who gets the highest salary. So If we write like this, we get just one of them. The result is wrong.
select D.Name, E.Name, max(E.Salary) from Employee as E, Department as D where D.Id = E.DepartmentId group by E.DepartmentId
I need to get the highest value first, then according to the value I get the ones whose salary is the value. So I use “IN” to help judge whether the pair of (Salary, DepartmentId) is the highest.
select D.Name, E.Name, E.Salary from Employee as E, Department as D where D.Id = E.DepartmentId and (E.Salary,E.DepartmentId) in (select MAX(Salary),DepartmentId from Employee group by DepartmentId)
The query from Table Department and from Subquery can be combined. I build a new table with columns (MAX_Salary, DepartmentId, DepartmentName). It will be quick.
select D.Name, E.Name, E.Salary from Employee as E, (select MAX(E1.Salary) as M_S, E1.DepartmentId, D1.Name from Employee E1, Department D1where E1.DepartmentId = D1.Id group by E1.DepartmentId) as D where E.salary = M_S and E.DepartmentId = D.DepartmentId
Adding “ORDER BY” in subTable will make query more quickly.
select D.Name, E.Name, E.Salary from Employee as E, (select MAX(E1.Salary) as M_S, E1.DepartmentId, D1.Name from Employee E1, Department D1where E1.DepartmentId = D1.Id group by E1.DepartmentIdorder by M_S) as D where E.salary = M_S and E.DepartmentId = D.DepartmentId
- LeetCode:Department Highest Salary
- Leetcode: Department Highest Salary
- LeetCode Department Highest Salary
- [Leetcode]Department Highest Salary
- Leetcode: Department Highest Salary
- [LeetCode][Database]Department Highest Salary
- Database LeetCode Department Highest Salary
- leetcode.184. Department Highest Salary
- Leetcode 184. Department Highest Salary
- Leetcode 184. Department Highest Salary
- [LeetCode] Department Highest & Top 3 Salary - SQL
- [LeetCode]Department Highest Salary,解题报告
- 【leetcode Database】184. Department Highest Salary
- 【LeetCode】Department Highest Salary && Department Top Three Salaries
- Department Highest Salary
- Department Highest Salary
- Department Highest Salary
- Department Highest Salary
- struts.properties配置详解
- Android异步加载全解析之引入一级缓存
- android json解析及简单例子
- CF 294 div2 D. A and B and Interesting Substrings (hash)
- 好玩的一些技术列表
- Leetcode: Department Highest Salary
- Liunx下安装ffmpeg,支持视频H264,音频MP3和AAC格式
- 深入剖析Java中的装箱和拆箱
- MySql和Java数据类型映射关系
- PetaPoco Sql 语法及使用
- 转一个常用排序算法的动画效果图
- HDOJ 畅通工程续 1874【简单最短路】
- Jmeter 压力测试
- 第二章第一题