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

Bootstrap 3.0 study notes CSS related supplement

The main contents of this article are as follows:...

Vue3 list interface data display details

Table of contents 1. List interface display examp...

Summary of Docker configuration container location and tips

Tips for using Docker 1. Clean up all stopped doc...

SystemC environment configuration method under Linux system

The following is the configuration method under c...

Detailed explanation of as, question mark and exclamation mark in Typescript

1. The as keyword indicates an assertion In Types...

Experience of redesigning the homepage of TOM.COM

<br />Without any warning, I saw news on cnB...

MySQL 5.7.18 zip version installation tutorial

The mysql 5.7.18 zip version of MySQL is not like...

An IE crash bug

Copy code The code is as follows: <style type=...

Do you know what are the ways to jump routes in Vue?

Table of contents The first method: router-link (...

React ref usage examples

Table of contents What is ref How to use ref Plac...

Interviewers often ask questions about React's life cycle

React Lifecycle Two pictures to help you understa...

How to use bind to set up DNS server

DNS (Domain Name Server) is a server that convert...

Detailed explanation of simple snow effect example using JS

Table of contents Preface Main implementation cod...