MySQL5.7 排序

diffiii

于 2024-08-02 08:08:32 发布

阅读量241

点赞数 10

分类专栏： MySQL 文章标签：数据库 sql

本文链接：https://blog.csdn.net/qq_45796594/article/details/140862418

版权

MySQL 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

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

select
col1,
col2,
@rank := @rank + 1 as rank 
from (
		select 'A' as col1,100 as col2
		union all 
		select 'B' as col1,130 as col2
		union all 
		select 'C' as col1,120 as col2
		union all 
		select 'D' as col1,120 as col2
		order 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 col2
		union all 
		select 'B' as col1,130 as col2
		union all 
		select 'C' as col1,120 as col2
		union all 
		select 'D' as col1,120 as col2
		order 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 col2
		union all 
		select 'B' as col1,130 as col2
		union all 
		select 'C' as col1,120 as col2
		union all 
		select 'D' as col1,120 as col2
		order by col2 desc
) a,(select @rownum:=0,@rank:=0,@col2 := NULL) b;

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

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

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


```dart
select 
c.col1,
c.col2,
c.rank
from (
	select
	obj.col1,
	obj.col2,
	IF(@col1 = col1, IF( @col2 = obj.col2, @rownum, @rownum := @rownum+1 ),@rownum :=1) as rank,
	@col1 := obj.col1,
	@col2 := obj.col2
	from (
		select 'A' as col1,100 as col2
		union all 
		select 'A' as col1,130 as col2
		union all 
		select 'A' as col1,130 as col2
		union all 
		select 'B' as col1,120 as col2
		union all 
		select 'B' as col1,110 as col2
		order 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 (
	select
	obj.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.col2
	from (
		select 'A' as col1,100 as col2
		union all 
		select 'A' as col1,130 as col2
		union all 
		select 'A' as col1,130 as col2
		union all 
		select 'B' as col1,120 as col2
		union all 
		select 'B' as col1,110 as col2
		order by col1,col2 desc
	) as obj,
	(select @rownum := 0,@rank := 0,@col1:=null,@col2 := null) r
) c;

三、指定顺序排序

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