Detailed explanation of the implementation example of group ranking in Mysql tutorial

Detailed explanation of the implementation example of group ranking in Mysql tutorial

1. Data Source

insert image description here

2. Overall ranking of data

1) General ranking

Starting from 1, the numbers are ranked downwards in order (the same value has a different ranking).

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

The results are as follows:

insert image description here

2) Tied ranking

Identical values ​​have the same rank (but no empty slots are left).

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 query score value = @price, output @rank,
 -- When they are not equal, 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 := score
from cs 
order by score desc) a;

The results are as follows:

insert image description here

3) Tied ranking

The same value has the same rank (but leaves a blank space).

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+1
from cs 
order by score desc) a;

The results are as follows:

insert image description here

3. Ranking within group after data grouping

1) Group general ranking

Starting from 1, the numbers are ranked downwards in order (the same value has a different ranking).

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

The results are as follows:

insert image description here

2) Tied ranking after grouping

Items with the same value within a group have the same ranking and do not occupy empty positions.

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 are as follows:

insert image description here

3) Tied ranking after grouping

Items with the same value within a group have the same ranking and need to occupy empty positions.

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 are as follows:

insert image description here

4. After grouping, take the top two of each group

① Method 1: Three ways of grouping and ranking, and then limiting 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) a
where a.rank<=2;

The results are as follows:

insert image description here

② Internal inquiry

SELECT * FROM cs c
WHERE (
    SELECT count(*) FROM cs
    WHERE c.city=cs.city AND c.score<cs.score )<2
ORDER BY city,score DESC;

The results are as follows:

insert image description here

The execution principle of the above code is as follows:

insert image description here

The above is the detailed content of the Mysql tutorial group ranking implementation example. For more information about Mysql group ranking, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • Count the classification of SQL statements in MySQL
  • MySQL classification ranking and grouping TOP N examples detailed explanation

<<:  Customize the style of the <input type="file"> element used when uploading files in HTML

>>:  Introduction to Linux common hard disk management commands

Recommend

Detailed example of concatenating multiple fields in mysql

The MySQL query result row field splicing can be ...

How to build a Vue3 desktop application

In this article, we will look at how to develop a...

Use Typescript configuration steps in Vue

Table of contents 1. TypeScript is introduced int...

Examples of using the ES6 spread operator

Table of contents What are spread and rest operat...

JavaScript super detailed implementation of web page carousel

Table of contents Creating HTML Pages Implement t...

Practice of el-cascader cascade selector in elementui

Table of contents 1. Effect 2. Main code 1. Effec...

Create a screen recording function with JS

OBS studio is cool, but JavaScript is cooler. Now...

Detailed tutorial on distributed operation of jmeter in docker environment

1. Build the basic image of jmeter The Dockerfile...

About Vue's 4 auxiliary functions of Vuex

Table of contents 1. Auxiliary functions 2. Examp...

How to manually install MySQL 5.7 on CentOS 7.4

MySQL database is widely used, especially for JAV...

Summary of Docker Data Storage

Before reading this article, I hope you have a ba...

Problems with nodejs + koa + typescript integration and automatic restart

Table of contents Version Notes Create a project ...