写点什么

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

  • 2022-12-05
    北京
  • 本文字数:882 字

    阅读完需:约 3 分钟

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

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.salaryFROM    employee e1        JOIN    department d ON e1.department_id = d.idWHERE    3 > (SELECT            COUNT(DISTINCT e2.salary)        FROM            employee e2        WHERE            e2.salary > e1.salary                AND e1.department_id = e2.department_id        );
复制代码


执行结果如下:



发布于: 刚刚阅读数: 4
用户头像

还未添加个人签名 2021-02-15 加入

还未添加个人简介

评论

发布
暂无评论
2022-12-05:部门工资前三高的所有员工。编写一个SQL查询找出每个部门中收入前三高的员工 。 +------------+----------+--------+ | Department |_福大大_福大大架构师每日一题_InfoQ写作社区