mysql中分组获取前三条记录的方法
转自: https://www.yisu.com/zixun/595682.html
这篇文章主要介绍mysql中分组获取前三条记录的方法,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!
要求:编写一个SQL,获取部门工资前三高的员工。
员工表和部门表结构:
CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255),
`salary` decimal(10,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;
员工表和部门表数据:
INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (1, 'Joe', 70000.00, 1);
INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (2, 'Henry', 80000.00, 2);
INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (3, 'Sam', 60000.00, 2);
INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (4, 'Max', 90000.00, 1);
INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (5, 'Janet', 69000.00, 1);
INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (6, 'Randy', 85000.00, 1);
INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (7, 'Eva', 85000.00, 1);
INSERT INTO `department`(`id`, `name`) VALUES (1, 'IT');
INSERT INTO `department`(`id`, `name`) VALUES (2, 'Sales');
题库的答案:
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(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
结果如下:
部门 员工 工资
IT Max 90000
IT Randy 85000
Sales Henry 80000
Sales Sam 60000
自己的理解
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department_id`) VALUES (1, 'Joe', 70000.00, 1);
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department_id`) VALUES (2, 'Henry', 80000.00, 2);
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department_id`) VALUES (3, 'Sam', 60000.00, 2);
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department_id`) VALUES (4, 'Max', 90000.00, 1);
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department_id`) VALUES (5, 'Janet', 69000.00, 1);
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department_id`) VALUES (6, 'Randy', 85000.00, 1);
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department_id`) VALUES (7, 'Eva', 86000.00, 1);
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department_id`) VALUES (8, 'Lucy', 76000.00, 2);
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department_id`) VALUES (9, 'Lilei', 100.00, 3);
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department_id`) VALUES (10, 'Hmm', 500.00, 3);
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department_id`) VALUES (11, 'Poly', 300.00, 3);
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department_id`) VALUES (12, 'Wang', 400.00, 3);
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department_id`) VALUES (13, 'Aime', 100.00, 3);
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department_id`) VALUES (14, 'Quu', 500.00, 3);
INSERT INTO `test`.`employee`(`id`, `name`, `salary`, `department_id`) VALUES (15, 'All', 500.00, 3);
INSERT INTO `test`.`department`(`id`, `name`) VALUES (1, 'IT');
INSERT INTO `test`.`department`(`id`, `name`) VALUES (2, 'Sales');
INSERT INTO `test`.`department`(`id`, `name`) VALUES (3, 'Cs');
查询每组最高工资的(一组中最高工资可能会有多条记录)
SELECT
e.*,
d.`name`
FROM
`employee` e
JOIN department d ON e.department_id = d.id
where ((select count(*) from employee ee where ee.department_id = e.department_id and e.salary < ee.salary) = 0)
解释:用到相关子查询,每遍历e表的工资,去找比e工资还高的,如果没有即count(*)=0,那么e表的该条记录的工资就是最高的
查询每组最低工资的(一组中最低工资可能会有多条记录)
SELECT
e.*,
d.`name`
FROM
`employee` e
JOIN department d ON e.department_id = d.id
where ((select count(*) from employee ee where ee.department_id = e.department_id and e.salary > ee.salary) = 0)
解释:用到相关子查询,每遍历e表的工资,去找比e工资还低的,如果没有即count(*)=0,那么e表的该条记录的工资就是最低的
查询每组最高工资的前两位(一组中工资可能会有多条记录)
SELECT
e.*,
d.`name`
FROM
`employee` e
JOIN department d ON e.department_id = d.id
where ((select count(*) from employee ee where ee.department_id = e.department_id and e.salary < ee.salary) <= 1)
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 推荐几款开源且免费的 .NET MAUI 组件库
· 实操Deepseek接入个人知识库
· 易语言 —— 开山篇
· Trae初体验