今天有个朋友问我:mysql 根据某个id进行分组,每个分组按时间排序取最新的一条记录,除了使用row_number函数,是否还有其他方法?
其实这是个老生常谈的问题了 ,并且会经常出现在各种面试题中,我给了这个朋友几个方案,他几乎没怎么考虑就选了最后一个:
方案1:使用row_number,配合cte写法,具体就是: with t as (select *,row_number() over (partition by id order by update_time desc) as rn from table )select * from t where id=1
方案2:先按id分组找到最新的更新时间写到临时表 ,再和原表关联,
CREATE TEMPORARY TABLE tmp_table as select id,max(update_time) as update_time from table group by id; select * from table a join tmp_table b on a.id=b.id and a.update_time=b.update_time
方案3:使用子查询作为查询条件
select * from table a where a.update_time =(select max(b.update_time ) from table b where b.id=a.id)
方案4:从业务层面优化,在表上加一个字段,isnewdata,bit型或者int型就好,每次在表中提交数据时,事务中先将要提交的数据所涉及的id,将历史数据isnewdata=1的更新为0,新提交的数据,isnewdata为1,然后增加一个索引isnewdata,或者如果需要和其他表关联的时候,增加复合索引,性能一下就上来了。
以上方案中:方案1和方案2中规中矩,是大多数人的选择;方案3性能最差,但是也有一些习惯用子查询的人采用;方案4性能最好,只是需要对业务做一些改进。
如果业务数据量不大,最多就几万条,用方案1,2,3均可。数据量不超过100万行,方案1和2还能勉强顶住。超过100万行,就要从业务层面去优化了,此时选择方案4是明智的