柚子快報(bào)激活碼778899分享：數(shù)據(jù)庫(kù) MySQL5.7 排序

http://yzkb.51969.com/

一、不分組排序 (1).排序-并列數(shù)據(jù)隨機(jī)順序

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).排序-并列數(shù)據(jù)排名相同且連續(xù)

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).排序-并列數(shù)據(jù)排名相同且不連續(xù)

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).分組排序-并列數(shù)據(jù)隨機(jī)順序

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).分組排名 -并列數(shù)據(jù)排名連續(xù)

```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).分組并列排名 -并列數(shù)據(jù)排名不連續(xù)

```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);

柚子快報(bào)激活碼778899分享：數(shù)據(jù)庫(kù) MySQL5.7 排序

http://yzkb.51969.com/

好文推薦