【leetcode Database】185. Department Top Three Salaries
来源:互联网 发布:umts是什么网络类型 编辑:程序博客网 时间:2024/05/29 16:31
题目:
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 |+------------+----------+--------+解析:
首先,按照部门Id和Salary对Employee表进行排序;之后,添加rank字段,为工资顺序序号。最后,再和Department表连接,得到最终数据。代码如下:
# Write your MySQL query statement belowSELECT d.Name AS Department, e.Name AS Employee,e.Salary AS Salary FROM (SELECT Name,Salary,DepartmentId, @Rank:=IF(@DepId != DepartmentId,1,IF(@Sal = Salary,@Rank,@Rank+1)) AS RANK, @DepId:=DepartmentId, @sal:= Salary FROM (SELECT Name,Salary,DepartmentId FROM Employee ORDER BY DepartmentId,Salary DESC) t, (SELECT @Rank := 0, @DepId := NULL, @sal := NULL) r) e INNER JOIN Department d ON e.DepartmentId = d.Id WHERE e.Rank <=3;
0 0
- leetcode-database-185. Department Top Three Salaries
- 【leetcode Database】185. Department Top Three Salaries
- [LeetCode][Database]Department Top Three Salaries
- LeetCode 185. Department Top Three Salaries
- leetcode 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 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][Database]Department Highest Salary
- Database LeetCode Department Highest Salary
- 【VBA研究】版本无关的取EXCEL有效行列数方法
- ztree树中的id,pid
- 域名那些事
- Android 自定义View UC下拉刷新效果(一)
- Android绘图进阶之Path详解
- 【leetcode Database】185. Department Top Three Salaries
- 求二叉树的深度
- 用基于信息熵的topsis方法实现学生成绩的综合排名
- 【华为 OJ 】字符串加密
- java synchronized关键字
- unity 滚动视图的优化
- 新手上路 layer弹层组件 (www.layui.com)
- IOS面试宝典(一)(持续更新)
- Python学习笔记1–Python概述