mysql分组查询第⼆条_mysql中的先排序后分组
引⾔：
今天同事翻看之前我写的sql时，问我我这个sql和他写的相⽐查询效率哪个更好。乍眼⼀看，竟然没看懂他写的sql,(⊙﹏⊙)b汗。仔细⼀看，还真是很巧妙，必须要研究研究！
所以便有了本篇内容:mysql如何先查询后分组(求每个分组的 top1)
问题重现：有这样⼀个需求，需要查询每个分组的某个字段最新(最⼤)对应的整条记录。举个栗⼦：假如有个员⼯表，有id(主键),salary(薪⽔),depart_id(部门id)，求出每个部门薪⽔最⾼的员⼯记录。
实现：
在这之前，我所知道⽐较简单明了的实现有下⾯这两种(为了简单，我创建了⼀个测试表，只包含排序字段和分组字段)
以下是建表语句
DROP TABLE IF EXISTS `sort_group`;
CREATE TABLE `sort_group` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sort` int(11) DEFAULT NULL,
`gp` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
insert into `sort_group`(`id`,`sort`,`gp`,`name`) values (1,1,1,'我是sort1,gp1'),(2,1,2,'我是sort1,gp2'),(3,2,1,'我是sort2,gp1'), (4,2,2,'我是sort2,gp2');
insert语句字段顺序表中的数据:
第⼀种实现：(先按正确的排序查询出的结果作为⼦查询，然后以⼦查询的结果集再分组，就会只剩下每个分组的第⼀条记录，以为⼦表是正确排序的，所以⼦表的每个分组的第⼀条记录就是想要的结果)
SELECT a.id,a.sort,a.gp,a.name FROM (
SELECT * FROM sort_group ORDER BY sort DESC
) a
GROUP BY a.gp
这种实现很好理解，按照语义就是先查询后排序。但是仔细⼀看，可以看出⼀点问题。⽤了分组查询，查的字段却没有都进⾏分组(这⾥指的是sort字段)，在⼀些数据库⽐如oracle，这段sql就会报错。mysql没有报错但是总有取巧的嫌疑。
测试结果:
第⼆种实现，利⽤group_concat()函数(使⽤GROUP_CONCAT)把gp相同的
分⼏步理解:
(1)利⽤GROUP_CONCAT把按照GROUP BY gp分组后形成的每条记录的sort字段以","组合起来，并且组合的sort是按照DESC排序的
SELECT GROUP_CONCAT(sort ORDER BY sort DESC),gp FROM sort_group GROUP BY gp;
结果:(第⼀条记录表⽰gp为1的分组由两个sort组成,sort分别为1,2，这⾥降序排列了，所以为2,1；同理第⼆条也是⼀样)
(2)接下来要做的就是把GROUP_CONCAT的组合字段再分解，分解后取第⼀个位置的值即可
SELECT SUBSTRING_INDEX(GROUP_CONCAT(sort ORDER BY sort DESC),',',1),gp FROM sort_group GROUP BY gp;
结果:
需要注意的是：如果需要取到每个sort对应的其他记录，我们来看下结果:
SELECT SUBSTRING_INDEX(GROUP_CONCAT(sort ORDER BY sort DESC),',',1),gp,id,NAME FROM sort_group GROUP BY gp;
结果:(我们会发现其他字段只是group by gp的第⼀条记录的，并不是sort为2对应的那条记录的值)
所以如果需要所有的字段可以考虑先查出每个分组下最⼤的记录对应的id，利⽤⼦查询将整条记录查出，如下:
(1).使⽤以sort的DESC排序，查询出ID值
SELECT GROUP_CONCAT(id ORDER BY sort DESC),gp FROM sort_group GROUP BY gp;
结果:
(2).利⽤SUBSTRING_INDEX把ID从CONCAT⾥⾯截取出来，再根据ID查询记录
SELECT * FROM `sort_group`
WHERE id IN
(SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY sort DESC),',',1) FROM sort_group GROUP BY gp);
查询结果:
第⼆种实现:(使⽤max函数和group by分组函数结合使⽤，这种⽅法的局限性也是不能直接查询出max值对应的该⾏的其他记录)
SELECT MAX(sort),gp FROM sort_group GROUP BY gp;
结果:
第三种实现:
SELECT a.*,b.sort,b.gp FROM sort_group a
LEFT JOIN sort_group b
ON a.gp = b.gp AND a.sort < b.sort
WHERE b.sort IS NULL
这种实现利⽤了左连接，乍⼀看很神奇是不是？ 原理将表根据分组字段进⾏⾃连接，然后根据a.sort < b.sort过滤连接，那么连接好的记录中，右表为空时，左表中的a.sort肯定是最⼤的，这样最后便得到了需求的记录(如果每组gp没有并列的最⼤记录，那么WHERE b.sort IS NULL 在每个不同的gp下只有⼀条记录，且是最⼤值；如假设每组gp⾥⾯的每个sort都是⼀样⼤，那么获取到的所有记录的b.sort都为null，且每个sort也是最⼤值)如图:
表中记录值:
SQL:
SELECT a.*,b.sort,b.gp FROM sort_group a
LEFT JOIN sort_group b
ON a.gp = b.gp AND a.sort < b.sort
查询结果:
假设每个相同的gp⾥⾯，对应的sort都是⼀样⼤的，此时更改表数据，如下:
SQL:
SELECT a.*,b.sort,b.gp FROM sort_group a
LEFT JOIN sort_group b
ON a.gp = b.gp AND a.sort < b.sort
显⽰结果(每组b.sort都是null，即每个记录⾥⾯的sort都是最⼤值):
效率:
下⾯测试⼀下在不建⽴索引的情况下执⾏效率。
为了⽅便模拟数据，本⼈写了⼀个存储函数模拟插⼊数据
DELIMITER $$
CREATE PROCEDURE `random_insert` (IN s int,IN g int,IN len int)
CONTAINS SQL
BEGIN
DECLARE i INT;
SET i = 0;
START TRANSACTION;
WHILE i <= len DO
INSERT into sort_group(sort,gp) VALUES (FLOOR(RAND() * s),FLOOR(RAND() * g));
SET i = i + 1;
END WHILE;
COMMIT;
END$$
DELIMITER ;
先测试每个组中平均有10条数据的情况，为了保证sort不重复，s值尽量⼤，执⾏以下语句：
call random_insert(1000000,10000,100000);
基于此运⾏3条sql，花费的时间分别是：
0.105s 0.095s 100+s(汗)
接下啦测试每组中平均有1000条的情况
call random_insert(1000000,100,100000);
0.126s 0.091s 100+s
然后我们给两个字段加上索引重复上⾯两次测试
0.106 0.135s 1000+s
0.101s 0.120s 100+s
从测试结果上看 第三种完全不可⽤，不难分析原因，第三种产⽣了笛卡尔积，并且⼀个组内数量越多，产⽣的越多。这⾥就不通过explain分析查询策略了，结果已经很明显了。
个⼈建议使⽤第⼆种来完成需求。当然这也不是绝对的，需要具体情况具体分析
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论