# Department Top Three Salaries

## Desicription

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  |
+------------+----------+--------+```

## Solution

```SELECT
D.Name as Department,
E.Name as Employee,
E.Salary
FROM
Department D,
Employee E
WHERE
E.DepartmentId = D.Id AND
(SELECT
COUNT(DISTINCT Salary)
FROM
Employee
WHERE
DepartmentId = D.Id AND
Salary > E.Salary) < 3```

0 条评论

• ### ​LeetCode刷题实战185：部门工资前三高的所有员工

算法的重要性，我就不多说了吧，想去大厂，就必须要经过基础知识和业务逻辑面试+算法面试。所以，为了提高大家的算法能力，这个公众号后续每天带大家做一道算法题，题目就...

• ### LeetCode MySQL 185. 部门工资前三高的所有员工（dense_rank）

Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。

• ### 【leetcode两题选手】MySQL类题目（五）

做完这些题，我觉得我得去系统的学一下SQL语句了。 有些题目，明明有自己的想法，却奈何格式写不出来啊，烦。

• ### MySQL题集

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：

• ### 数据科学工程师面试准备指南（国际版）

与其他技术面试一样，数据科学面试也需要大量的准备工作。比如你需要知道统计、编程和机器学习的知识。今天就为大家盘点一下该做什么样的准备。

• ### 【Java】：Apache Commons Digester

Many projects read XML configuration files to provide initialization of various ...

• ### 【Leetcode 70】关关的刷题日记68 – Leetcode 70 Climbing Stairs

关关的刷题日记68 – Leetcode 70 Climbing Stairs 题目 You are climbing a stair case. It tak...

• ### 【Leet Code】70. Climbing Stairs

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。 ...

• ### 预告 | 投稿倒计时7天：第十届国际图象图形学学术会议（ICIG 2019）征稿进行中

AI 科技评论消息，第十届国际图象图形学学术会议（ICIG 2019）将于 2019 年 8 月 23-25 日在北京友谊宾馆召开，主题为「人工智能时代的图像图...

• ### 计算机视觉学术速递[6.24]

【1】 Transformer Meets Convolution: A Bilateral Awareness Net-work for Semantic ...

• ### 金融/语音/音频处理学术速递[8.24]

【1】 Effect of Share Capital on Financial Growth of Non-Financial Firms Listed a...

• ### 统计学学术速递[8.19]

【1】 Geometry-informed irreversible perturbations for accelerated convergence of...

• ### 计算机视觉学术速递[8.24]

【1】 Discovering Spatial Relationships by Transformers for Domain Generalization...

• ### 自然语言处理学术速递[8.20]

【1】 Attentive fine-tuning of Transformers for Translation of low-resourced lang...

• ### [Repost]A Survival Guide to a PhD

This guide is patterned after my “Doing well in your courses”, a post I wrote a ...

• ### 企业 IT 的开源最佳实践

由吉廷阿加瓦尔» Linux 内幕人士 » ECT 新闻网 2020 年 9 月 1 日下午 12：32 PT

• ### 算法细节系列（16）：深度优先搜索

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.n...

• ### 金融/语音/音频处理学术速递[7.7]

【1】 Countering Misinformation on Social Media Through Educational Interventions...

### Reck Zhang

TencentGame Developer

Tencent · Game Developer (已认证)