184. Department Highest Salary
来源:互联网 发布:vincent知乎 编辑:程序博客网 时间:2024/06/04 18:23
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 |+------------+----------+--------+
Solution
SELECT Name as Department, Employee, SalaryFROM Department JOIN (SELECT Employee.Name AS Employee, Employee.Salary, Employee.DepartmentId FROM Employee INNER JOIN (SELECT DepartmentId, MAX(Salary) AS Salary FROM Employee GROUP BY DepartmentId) t1 ON t1.DepartmentId = Employee.DepartmentId AND t1.Salary = Employee.Salary) t2 ON Department.Id = t2.DepartmentId
Note: 1. 这道题在解的时候要先选择department表格,因为这个表格比employee表格小,在外面会更快。2. 两次join 都可以用inner join 或者 join,但递交leetcode时间显示是外面用join 里面用inner join更快。
Note on Markdown:1. ```昨天高亮部分的三点是copy的模板里的,今天知道了它从键盘上哪儿来,就是敲那个跟 ~ 在一起的键六次:敲三次,然后空格留白,再敲三次,这样6个点覆盖的部分就高亮了。2. CSDN页面字体显示很崩溃,代码在他们的编辑器里显示是courier的样子,但是到网页上就变成了类似times 的字体 ==!
0 0
- 184. Department Highest Salary
- 184. Department Highest Salary
- 184. Department Highest Salary
- 184. Department Highest Salary
- leetcode.184. Department Highest Salary
- Leetcode 184. Department Highest Salary
- Leetcode 184. Department Highest Salary
- 184. Department Highest Salary#1
- 【leetcode Database】184. Department Highest Salary
- LeetCode:Department Highest Salary
- Leetcode: Department Highest Salary
- LeetCode Department Highest Salary
- [Leetcode]Department Highest Salary
- Department Highest Salary
- Leetcode: Department Highest Salary
- Department Highest Salary
- Department Highest Salary
- Department Highest Salary
- ipadmini锁屏密码破解怎么激活ID?
- MySQL 中NULL和空字符串的区别
- 浅谈android中的异步加载一
- python科学计算
- 基本数据类型
- 184. Department Highest Salary
- 基于Spring Boot和Spring Cloud实现微服务架构学习
- hive调优笔记
- 我遇到的android6.0闪退 没有正确提示错误
- Cocoa深入学习:NSOperationQueue、NSRunLoop和线程安全
- VS2012调用libcurl库
- 数据类型的转换
- Android 4.0 Launcher2源码分析——桌面快捷图标的拖拽
- 垃圾收集与内存分配策略