The method of getting the first three records by grouping in mysql

This article mainly introduces the method of grouping to obtain the first three records in mysql, which is very detailed and has a certain reference value. Interested friends must read it!

Requirements: write a SQL to get the top three highest-paid employees in the department.

Employee table and department table structure:

CREATE TABLE `employee` (

`id`int (11) NOT NULL AUTO_INCREMENT

`name` varchar (255)

`salary` decimal (10pm 2)

`department_ id` int (11)

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE `department` (

`id`int (11) NOT NULL AUTO_INCREMENT

`name` varchar (255)

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Employee table and department table data:

INSERT INTO `employee` (`id`, `name`, `salary`, `salary`) VALUES (1, 'Joe', 70000.00, 1)

INSERT INTO `employee` (`id`, `name`, `salary`, `salary`) VALUES (2, 'Henry', 80000.00, 2)

INSERT INTO `employee` (`id`, `name`, `salary`, `salary`) VALUES (3, 'Sam', 60000.00, 2)

INSERT INTO `employee` (`id`, `name`, `salary`, `salary`) VALUES (4, 'Max', 90000.00, 1)

INSERT INTO `employee` (`id`, `name`, `salary`, `salary`) VALUES (5, 'Janet', 69000.00, 1)

INSERT INTO `employee` (`id`, `name`, `salary`, `salary`) VALUES (6, 'Randy', 85000.00, 1)

INSERT INTO `employee` (`id`, `name`, `salary`, `salary`) VALUES (7, 'Eva', 85000.00, 1)

INSERT INTO `department` (`id`, `name`) VALUES (1, 'IT')

INSERT INTO `department` (`id`, `name`) VALUES (2, 'Sales')

Answers to the question bank:

SELECT

D.`name`AS 'Department'

E.`name`AS 'employee'

E.salary AS 'salary'

FROM

Employee e

JOIN department d ON d.id = e.department_id

WHERE

(

SELECT count (DISTINCT em.salary) FROM employee em WHERE em.salary > e.salary AND em.department_id = e.department_id

)

< 3 ORDER BY e.department_id, e.salary DESC 输出结果如下： 部门 员工 工资 IT Max 90000 IT Randy 85000 IT Eva 85000 IT Joe 70000 Sales Henry 80000 Sales Sam 60000 首先来理解一下上面的 SQL，当 < 3 的条件改为 = 0 时，即子表中相同部门没有比主表工资高的员工，则取得工资最高的员工；当条件为 = 1 时，表示子表中相同部门里只有一个比主表工资高的员工，则取得工资第二高的员工；同理，条件 = 2 表示工资第三高的员工，所以工资前三高的员工的条件为 < 3。 通过结果可以看到，第二名员工和第三名员工工资相同，被当作并列第二，并不会排挤掉第三名。如果我们希望出现并列第二名时，第三名就变成第四名呢？可以把 count(DISTINCT em.salary) 改成 count(*)。 SELECT d.`name` AS '部门', e.`name` AS '员工', e.salary AS '工资' FROM employee e JOIN department d ON d.id = e.department_id WHERE ( SELECT count(*) FROM employee em WHERE em.salary >

E.salary AND em.department_id = e.department_id

)

< 3 ORDER BY e.department_id, e.salary DESC 输出结果： 部门 员工 工资 IT Max 90000 IT Randy 85000 IT Eva 85000 Sales Henry 80000 Sales Sam 60000 上面的写法中，当我们取前两名时，会得到 IT 部门的第一名和两个第二名的员工。如果我们希望去掉并列的情况，即就算工资相同也分为不同名次呢？那可以根据工资排序来增加多一个序号列，把 employee 表替换成下面这个子表： SELECT (@i:=@i+1) AS rownum, es.* FROM employee es, (select @i:=0) ri ORDER BY es.salary 1 然后去查询每个部门工资前两名的员工，这里注意一下，两个子表变量名需要不一样： SELECT d.`name` AS '部门', e.`name` AS '员工', e.salary AS '工资' FROM (SELECT (@i:=@i+1) AS rownum, es.* FROM employee es, (select @i:=0) ri ORDER BY es.salary) e JOIN department d ON d.id = e.department_id WHERE ( SELECT count(*) FROM (SELECT (@j:=@j+1) AS rownum, es.* FROM employee es, (select @j:=0) rj ORDER BY es.salary) em WHERE em.rownum >

E.rownum AND em.department_id = e.department_id

) < 2

ORDER BY e.department_id, e.salary DESC

The results are as follows:

Salary of departmental staff

IT Max 90000

IT Randy 85000

Sales Henry 80000

Sales Sam 60000

The above is all the contents of the article "how to get the first three records in groups in mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!