当前位置：首页 > news >正文

MySQL5.7 排序

news 2025/7/7 16:40:40

一、不分组排序
(1).排序-并列数据随机顺序

select
col1,
col2,
@rank := @rank + 1 as rank 
from (select 'A' as col1,100 as col2union all select 'B' as col1,130 as col2union all select 'C' as col1,120 as col2union all select 'D' as col1,120 as col2order by col2 desc
) a,(select @rank:=0) b;

(2).排序-并列数据排名相同且连续

select
a.col1,
a.col2,
if( @col2 = a.col2, @rank, @rank := @rank + 1 ) as rank,
@col2 := a.col2
from (select 'A' as col1,100 as col2union all select 'B' as col1,130 as col2union all select 'C' as col1,120 as col2union all select 'D' as col1,120 as col2order by col2 desc
) a,(select @rank:=0, @col2 := NULL) b;

(3).排序-并列数据排名相同且不连续

select
a.col1,
a.col2,
@rownum := @rownum + 1,
if( @col2 = a.col2, @rank, @rank := @rownum) as rank,
@col2 := a.col2
from (select 'A' as col1,100 as col2union all select 'B' as col1,130 as col2union all select 'C' as col1,120 as col2union all select 'D' as col1,120 as col2order by col2 desc
) a,(select @rownum:=0,@rank:=0,@col2 := NULL) b;

二、分组排序
(1).分组排序-并列数据随机顺序

select 
c.col1,
c.col2,
c.rank
from (selecta.col1,a.col2,@rownum:=@rownum+1,if(@col1=a.col1,@rank:=@rank+1,@rank:=1) as rank,@col1:=a.col1from (select 'A' as col1,100 as col2union all select 'A' as col1,130 as col2union all select 'B' as col1,120 as col2union all select 'B' as col1,110 as col2order by col1,col2 desc) a,(select @rownum:=0,@col1:=null,@rank:=0) b
) c ;

(2).分组排名 -并列数据排名连续


```dart
select 
c.col1,
c.col2,
c.rank
from (selectobj.col1,obj.col2,IF(@col1 = col1, IF( @col2 = obj.col2, @rownum, @rownum := @rownum+1 ),@rownum :=1) as rank,@col1 := obj.col1,@col2 := obj.col2from (select 'A' as col1,100 as col2union all select 'A' as col1,130 as col2union all select 'A' as col1,130 as col2union all select 'B' as col1,120 as col2union all select 'B' as col1,110 as col2order by col1,col2 desc) as obj,(select @rownum := 0,@col1:=null,@col2 := null) r
) c;


(3).分组并列排名 -并列数据排名不连续```dart
select 
c.col1,
c.col2,
c.rank
from (selectobj.col1,obj.col2,if(@col1 = obj.col1, @rownum := @rownum + 1, @rownum :=1),if(@col1 = obj.col1, if(@col2 = obj.col2, @rank, @rank := @rownum ),@rank :=1) as rank,@col1 := obj.col1,@col2 := obj.col2from (select 'A' as col1,100 as col2union all select 'A' as col1,130 as col2union all select 'A' as col1,130 as col2union all select 'B' as col1,120 as col2union all select 'B' as col1,110 as col2order by col1,col2 desc) as obj,(select @rownum := 0,@rank := 0,@col1:=null,@col2 := null) r
) c;

三、指定顺序排序

select * 
from (select 1 as idunion all select 2 as idunion all select 3 as idunion all select 4 as idunion all select 5 as idunion all select 6 as idunion all select 7 as id
) a
where `id` IN (1, 7, 3, 5) 
order by FIELD(`id`, 5, 3, 7, 1);