【mysql5.7】组内分组排序

介绍

一般地, 组内排序取最新数据时,会采用类似这样的写法:

select * from table1 as pmh,
          ( SELECT ponding_id,MAX(create_time) create_time
                 FROM table1  GROUP BY ponding_id ) as t1
    where t1.ponding_id = pmh.ponding_id and pmh.create_time = t1.create_time;

---

这里呢 ,我要介绍另一种方法:

SUBSTRING_INDEX( GROUP_CONCAT( 唯一字段1 ORDER BY 排序字段2 DESC ), ',', 值(取第几个) )

举例:

有一个这样的表:

CREATE TABLE `wisdom_well_cover` (
  `id` varchar(36) NOT NULL COMMENT '主键',
  `device_id` varchar(32) DEFAULT NULL COMMENT '设备编号',
  `device_type` varchar(32) DEFAULT NULL COMMENT '设备类型',
  `timestamp` datetime DEFAULT NULL COMMENT '接收时间'
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

需求:

查找每个设备的最新数据

select wwc.* from wisdom_well_cover wwc inner join (
	SELECT SUBSTRING_INDEX ( 
		GROUP_CONCAT( id ORDER BY TIMESTAMP DESC ), ',', 1 
	) AS id 
	FROM wisdom_well_cover GROUP BY device_id
) as t1 on wwc.id = t1.id

查找每个设备第二新的数据

select wwc.* from wisdom_well_cover wwc inner join (
	SELECT SUBSTRING_INDEX (
		SUBSTRING_INDEX ( 
			GROUP_CONCAT( id ORDER BY TIMESTAMP DESC ), ',', 2 
			),',',-1) AS id 
	FROM wisdom_well_cover GROUP BY device_id
) as t1 on wwc.id = t1.id

1. SUBSTRING_INDEX 字符串截取
2. GROUP_CONCAT 这个比较奇特 , 可以对分组连接后的字段进行排序,去重等等,
   具体可以自行学习. 连接后的各个字符串是用逗号分割的, 所以用到了字符串截取.

性能对比

这里有一张13万数据的表

表结构如下:

CREATE TABLE `ponding_model_history` (
  `id` varchar(36) NOT NULL,
  `create_by` varchar(50) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建日期',
  `acquisition_time` datetime DEFAULT NULL COMMENT '采集时间',
  ....此处省略其他字段....
  PRIMARY KEY (`id`) USING BTREE,
  KEY `acquisition_time_index` (`acquisition_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

用两种方法进行分组排序

• 第一种 : 用时: 1.349s

  select * from ponding_model_history as pmh,
        ( SELECT ponding_id,MAX(create_time) create_time
               FROM ponding_model_history 
               GROUP BY ponding_id ) as t1
  where t1.ponding_id = pmh.ponding_id and pmh.create_time = t1.create_time;
  

• 第二种 用时: 0.682s

  select * from ponding_model_history as pmh INNER JOIN 
        ( SELECT SUBSTRING_INDEX( GROUP_CONCAT( id ORDER BY create_time DESC ), ',', 1 ) id
              FROM ponding_model_history 
              GROUP BY ponding_id ) as t1
  on t1.id = pmh.id ;
  

经过验证 结果两种方法结果都无误