mysq分组内排序

#分组第一名
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;

标签: none

添加新评论