Department Highest Salary
来源:互联网 发布:创新创业 网络视频课 编辑:程序博客网 时间:2024/06/07 01:04
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 |+------------+----------+--------+
题意:有两张表Employee和Department,求各个部分中工资最高的。
思路:先根据DepartmentID分组计算出最大值,得到临时表,再与Employee表内连接,再与Deparment表内连接
SELECT d.Name AS Department, e.Name AS Employee, t.Salary FROMEmployee e INNER JOIN (SELECT DepartmentId, MAX(Salary) AS Salary FROM Employee GROUP BY DepartmentId) tUSING(DepartmentId, Salary) #等价于e.DepartmentId = t.DepartmentId and e.Salary = t.SalaryINNER JOIN Department dON d.id = t.DepartmentId
0 0
- 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
- 184. Department Highest Salary
- MySQL-Department Highest Salary
- 184. Department Highest Salary
- 184. Department Highest Salary
- 184. Department Highest Salary
- [LeetCode][Database]Department Highest Salary
- Database LeetCode Department Highest Salary
- leetcode.184. Department Highest Salary
- Leetcode 184. Department Highest Salary
- Blue Dragon版的CFOUPPUT的GROUP问题的一个奇异解决方法。
- ubuntu开启sshd服务(转载)
- 单链表查找中间元素
- NTLM---NT Lan manager
- 计算代码执行时间
- Department Highest Salary
- 服务器被黑给我上了一课
- sqlserver -getdate()日期格式化大全
- google magager
- leetcode - Integer to Roman 与 Roman to Integer
- 三个网站让你成为Linux命令行高手
- volley获取网络图片的一些方法
- 开博
- POI操作的一些样式设置