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
Mac uses Shell (Terminal) SSH to connect to the r...
The main contents of this article are as follows:...
Table of contents 1. List interface display examp...
Preface Every time I use the terminal to create a...
Tips for using Docker 1. Clean up all stopped doc...
The following is the configuration method under c...
1. The as keyword indicates an assertion In Types...
<br />Without any warning, I saw news on cnB...
The mysql 5.7.18 zip version of MySQL is not like...
Copy code The code is as follows: <style type=...
Table of contents The first method: router-link (...
Table of contents What is ref How to use ref Plac...
React Lifecycle Two pictures to help you understa...
DNS (Domain Name Server) is a server that convert...
Table of contents Preface Main implementation cod...