2022-12-05:部门工资前三高的所有员工。编写一个SQL查询找出每个部门中收入前三高的员工

2022-12-05:部门工资前三高的所有员工。编写一个SQL查询找出每个部门中收入前三高的员工 。
+————+———-+——–+
| Department | Employee | Salary |
+————+———-+——–+
| IT | Max | 90000 |
| IT | Joe | 85000 |
| IT | Randy | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+————+———-+——–+

DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `department` VALUES ('1', 'IT');
INSERT INTO `department` VALUES ('2', 'Sales');

DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `salary` int(11) NOT NULL,
  `department_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `employee` VALUES ('1', 'Joe', '85000', '1');
INSERT INTO `employee` VALUES ('2', 'Henry', '80000', '2');
INSERT INTO `employee` VALUES ('3', 'Sam', '60000', '2');
INSERT INTO `employee` VALUES ('4', 'Max', '90000', '1');
INSERT INTO `employee` VALUES ('5', 'Janet', '69000', '1');
INSERT INTO `employee` VALUES ('6', 'Randy', '85000', '1');
INSERT INTO `employee` VALUES ('7', 'Will', '70000', '1');

答案2022-12-05:

sql语句如下:




























SELECT
    d.Name AS 'department', e1.Name AS 'employee', e1.salary
FROM
    employee e1
        JOIN
    department d ON e1.department_id = d.id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.salary)
        FROM
            employee e2
        WHERE
            e2.salary > e1.salary
                AND e1.department_id = e2.department_id
        )
;

执行结果如下:

在这里插入图片描述

本作品采用《CC 协议》,转载必须注明作者和本文链接
微信公众号:福大大架构师每日一题。最新面试题,涉及golang,rust,mysql,redis,云原生,算法,分布式,网络,操作系统。
讨论数量: 2
TimJuly

不严谨,IT 部门选出来 4 个人。

1个月前 评论
Thinker_Blog 1个月前

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!