介绍

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

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 ;

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