1. Data Source2. Overall ranking of data1) General rankingStarting 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: 2) Tied rankingIdentical 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: 3) Tied rankingThe 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: 3. Ranking within group after data grouping1) Group general rankingStarting 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: 2) Tied ranking after groupingItems 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: 3) Tied ranking after groupingItems 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: 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: ② 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: The execution principle of the above code is as follows: 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:
|
<<: Customize the style of the <input type="file"> element used when uploading files in HTML
>>: Introduction to Linux common hard disk management commands
The MySQL query result row field splicing can be ...
In this article, we will look at how to develop a...
Table of contents 1. TypeScript is introduced int...
Table of contents What are spread and rest operat...
Table of contents Creating HTML Pages Implement t...
First, the principle of esp8266 publishes message...
Table of contents 1. Effect 2. Main code 1. Effec...
Since I returned the Mac, my original laptop has ...
OBS studio is cool, but JavaScript is cooler. Now...
1. Build the basic image of jmeter The Dockerfile...
Table of contents 1. Auxiliary functions 2. Examp...
MySQL database is widely used, especially for JAV...
Before reading this article, I hope you have a ba...
Table of contents Version Notes Create a project ...
summary: The following is a method for changing t...