Example Analysis of Mysql data packet ranking implementation

Mysql data grouping ranking implementation example analysis, many novices are not very clear about this, in order to help you solve this problem, the following small series will explain in detail for everyone, there are people who need this to learn, I hope you can gain something.

1. data source

2. Overall ranking 1) General ranking

Starting with 1, move down in order (same values are also different rankings).

set @rank =0;select city , score, @rank := @rank+1 rank from cs order by score desc;

The results were as follows:

2) Parallel ranking

The same value is the same rank (but no empty space).

set @rank=0,@price=null;select cs.* , case when @price = score then @rank when @price := score then @rank := @rank+1 end rank from cs order by score desc; --When the score value of the query = @price, output @rank, --When unequal, assign the score value to @price and output @rank := @rank+1 --or set @rank=0,@price=null;select a.city,a.score,a.rank from (select cs.*, @rank := if(@p=score,@rank,@rank+1) rank, @p := scorefrom cs order by score desc) a;

The results were as follows:

3) Parallel ranking

The same value is the same rank (but with spaces left).

set @rank=0,@price=null, @z=1;select a.city,a.score,a.rank from (select cs.*, @rank := if(@p=score,@rank,@z) rank, @p := score,@z :=@z+1from cs order by score desc) a;

The results were as follows:

3. Rank within the group after grouping 1) Rank in the group

Starting with 1, move down in order (same values are different rankings).

set @rank=0,@c=null;select cs.city,cs.score, @rank := if(@c = city,@rank+1,1) rank, @c := cityfrom cs order by cs.city,cs.score;

The results were as follows:

2) Parallel ranking after grouping

The same values rank the same within the group and do not occupy vacancies.

set @rank=0,@c=null,@s=null;select cs.city,cs.score, @rank := if(@c=city,if(@s=score,@rank,@rank+1),1) rank , @c := city, @s :=score from cs order by cs.city,cs.score;

The results were as follows:

3) Parallel ranking after grouping

The same values rank the same within the group and need to occupy empty places.

set @rank=0,@c=null,@s=null;select cs.city,cs.score, @rank := if(@c=city,if(@s=score,@rank,@rank+1),1) rank , @c := city, @s :=score from cs order by cs.city,cs.score;

The results were as follows:

4. After grouping, select the top two in each group

① Method 1: According to the three ways of grouping ranking, and then limit the ranking value

set @rank=0,@z=0,@c=null,@s=null;select a.city,a.score,a.rank from (select cs.city city,cs.score score, @z := if(@c=city,@z+1,1), @rank := if(@c=city,if(@s=score,@rank,@z),1) rank, @c := city, @s :=score from cs order by cs.city,cs.score desc) awhere a.rank