专栏首页Reck ZhangLeetCode 0185 - Department Top Three Salaries

LeetCode 0185 - Department Top Three Salaries

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:部门工资前三高的所有员工

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

    程序IT圈
  • LeetCode MySQL 185. 部门工资前三高的所有员工(dense_rank)

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

    Michael阿明
  • 【leetcode两题选手】MySQL类题目(五)

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

    看、未来
  • MySQL题集

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

    看、未来
  • 数据科学工程师面试准备指南(国际版)

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

    HuangWeiAI
  • 【Java】:Apache Commons Digester

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

    WEBJ2EE
  • leetcode-506-Relative Ranks

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

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

    WZEARW
  • 【Leet Code】70. Climbing Stairs

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

    韩旭051
  • 预告 | 投稿倒计时7天:第十届国际图象图形学学术会议(ICIG 2019)征稿进行中

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

    AI科技评论
  • 计算机视觉学术速递[6.24]

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

    公众号-arXiv每日学术速递
  • 金融/语音/音频处理学术速递[8.24]

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

    公众号-arXiv每日学术速递
  • 统计学学术速递[8.19]

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

    公众号-arXiv每日学术速递
  • 计算机视觉学术速递[8.24]

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

    公众号-arXiv每日学术速递
  • 自然语言处理学术速递[8.20]

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

    公众号-arXiv每日学术速递
  • [Repost]A Survival Guide to a PhD

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

    昊楠Hacking
  • 企业 IT 的开源最佳实践

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

    zstt8054929
  • 算法细节系列(16):深度优先搜索

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

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

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

    公众号-arXiv每日学术速递

扫码关注云+社区

领取腾讯云代金券