编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE Employee (
	id int,
	name varchar(255),
	salary int,
	departmentId int
);
CREATE TABLE Department (
	id int,
	name varchar(255)
);

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, 'jet',  69000, 1);
INSERT INTO Employee VALUES (6, 'Randy',  90000, 1);
INSERT INTO Employee VALUES (7, 'Will',  70000, 1);

INSERT INTO Department VALUES (1, 'IT');
INSERT INTO Department VALUES (2, 'sales');

SELECT 
    d.name AS Department,
    e.name AS Employee,
    e.salary AS Salary
FROM (
    SELECT 
        name,
        departmentId,
        salary,
        @rank := IF(@dept = departmentId, 
                    IF(@prev_salary = salary, @rank, @rank + 1), 
                    1) AS s1,
        @dept := departmentId,
        @prev_salary := salary
    FROM Employee, 
        (SELECT @rank := 0, @dept := NULL, @prev_salary := NULL) AS vars
    ORDER BY departmentId, salary DESC
) AS e
INNER JOIN Department AS d 
    ON d.id = e.departmentId
WHERE e.s1 <= 3;
;