mysql中的先排序后分组

最新推荐文章于 2023-08-11 11:44:46 发布

chetui2456

最新推荐文章于 2023-08-11 11:44:46 发布

阅读量977

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/u/2331760/blog/3073664

版权

引言：
今天同事翻看之前我写的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');

表中的数据:

第一种实现：(先按正确的排序查询出的结果作为子查询,然后以子查询的结果集再分组,就会只剩下每个分组的第一条记录,以为子表是正确排序的,所以子表的每个分组的第一条记录就是想要的结果)

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分析查询策略了，结果已经很明显了。

个人建议使用第二种来完成需求。当然这也不是绝对的，需要具体情况具体分析

转载于:https://my.oschina.net/u/2331760/blog/3073664