leetcode 185. Department Top Three Salaries
来源:互联网 发布:淘宝能参加聚划算吗 编辑:程序博客网 时间:2024/06/05 17:09
The Employee
table holds all employees. Every employee has an Id, 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 || 5 | Janet | 69000 | 1 || 6 | Randy | 85000 | 1 |+----+-------+--------+--------------+
The Department
table holds all departments of the company.
+----+----------+| Id | Name |+----+----------+| 1 | IT || 2 | Sales |+----+----------+
Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows.
+------------+----------+--------+| Department | Employee | Salary |+------------+----------+--------+| IT | Max | 90000 || IT | Randy | 85000 || IT | Joe | 70000 || Sales | Henry | 80000 || Sales | Sam | 60000 |+------------+----------+--------+
CREATE TABLE [Employees]([Id] [int] NOT NULL Primary Key,[Name] [nvarchar](20) NOT NULL,[Salary] decimal NOT NULL,[DepartmentId] [int] NOT NULL)insert into [Employees] values(1,'Joe',70000,1),(2,'Henry',80000,2),(3,'Sam',60000,2),(4,'Max',90000,1),(5,'Janet',69000,1),(6,'Randy',85000,1);CREATE TABLE [Department]([Id] [int] NOT NULL Primary Key,[Name] [nvarchar](20) NOT NULL)insert into [Department] values(1,'IT'),(2,'Sales');with temp as(select e.Id,e.Name Employee,e.Salary,e.DepartmentId,D.Name Department,RN=ROW_NUMBER() over(PARTITION BY d.Id order by e.salary desc)from [Employees] e inner join [Department] d on e.DepartmentId=d.Id)select temp.Department,temp.Employee,temp.Salary from temp where RN<4order by temp.DepartmentId,temp.RN
0 0
- LeetCode 185. Department Top Three Salaries
- leetcode-database-185. Department Top Three Salaries
- leetcode 185. Department Top Three Salaries
- 【leetcode Database】185. Department Top Three Salaries
- leetcode 185. Department Top Three Salaries
- [leetcode] Department Top Three Salaries
- LeetCode Department Top Three Salaries
- 185. Department Top Three Salaries
- Department Top Three Salaries
- Department Top Three Salaries
- [LeetCode][Database]Department Top Three Salaries
- leetcode 184,185 Department Top Three Salaries/HIGHEST Salaries
- 【LeetCode】Department Highest Salary && Department Top Three Salaries
- leetcode数据库sql之Department Top Three Salaries
- LeetCode_OJ【185】Department Top Three Salaries
- 题目9:MySQL---------Department Top Three Salaries
- [LeetCode] Department Highest & Top 3 Salary - SQL
- LeetCode:Department Highest Salary
- AutoTools 傻瓜入门
- php关联数组操作
- 干货分享:SparkBench--Spark平台的基准性能测试
- 去掉VASSISTX的红色下划线
- 数的进制
- leetcode 185. Department Top Three Salaries
- L1范数正则化
- 6.jvm之自定义安全管理器
- 0/1背包-背包问题程序代码(pascal)
- UIBezierPath 画线 圆 弧
- 报表或BI的价值在哪?
- Java-----instanceof、isInstance、isAssignableFrom
- WPF性能优化经验总结
- structs2拦截器原理