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.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

        )

;

执行结果如下：

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