惊蛰 - 2020年12月
type: channel
link: http://forthxu.com/blog/2020/12/
description: Just So So ...
items: mysq分组内排序

mysq分组内排序
type: item
date: 2020-12-10T18:35:35+08:00
description: #分组第一名 select * from birecord where platform='okex-swap' and concat(mark, addtime) in (select concat(mark, max(addtime)) from birecord where platform='okex-swap' group by mark) #分组内前两名，查询速度慢 select * from birecord m where platform='okex-swap' and (select count(*) from birecord n where platform='okex-swap' and n.mark=m.mark and n.addtime>m.addtime)<2; #分组内前两名，addtime值前两名，可能超过两名，mysql8.0后支持 select * from (select *,row_number() over(partition by mark order by addtime) as num from birecord where platform='okex-swap') n where num<3; #分组内前两名，addtime排序前两名，mysql8.0后支持 select * from (select *,rank() over(partition by mark order by addtime) as num from birecord where platform='okex-swap') n where num<3; #可以标记顺序但做不了分组排序 select *,(@row_num:=@row_num+1) as num from birecord,(select @row_num:=0) as t group by mark;