Source of this articleMySQL ranking function implementation
Reprint, please indicate the source
Now there is a need to rank all students’ scores and list their ranking. Just seeing this demand, I was a little confused and had no ideas at all. Why wouldn’t I do it if it was more difficult? Go online to query information, list all the implementations, and learn all of them.
Create a score table s_ score
CREATE TABLE `s_score` ( `id` int NOT NULL AUTO_INCREMENT, `score` int NOT NULL DEFAULT 0, `name` varchar(20) CHARACTER SET utf8mb4 NULL, PRIMARY KEY (`id`) );
INSERT INTO `s_score` (`name`, `score`) VALUES ('Zhang San', 80), ('Xiao Ming', 90), ('little red', 60), ('Li Si', 70), ('Zhao Wu', 80), ('Liang Chen', 87), ('little green', 69), ('William', 69), ('David', 91), ('Wang Wu', 96), ('Zhao Liu', 96), ('little five', 80), ('Little Dragon', 88);
The rank ranking function rank is launched in MySQL 8.0, which fully supports this requirement, but this feature must be supported by MySQL 8.0 or above. What is the implementation method for versions below 8.0? Use user variables to record the ranking.
User variables: in“
@var_name“, to distinguish between user variables and column names. It can be any random, compound scalar expression as long as no column is specified. Here is a small example to show how to use user variables
select @a:=1 a,@b:[email protected]+1 b
results of enforcement
: = means assignment, which is a little different from programming language assignment. Let’s start to show the effect of using simple SQL to implement rank ranking function
The user variable simply realizes the rank display
SELECT name,score, @rank:[email protected]+1 `rank` from s_score s,(select @rank:=0) q ORDER BY score desc
And ranking display
Now there is another problem. If the scores are the same, the ranking should be the same. We use a temp variable to record the previous score value to judge whether the previous score is equal to the current score. If it is equal, it will directly return to the previous ranking, otherwise the ranking will be + 1.
select name,score,case when @temp_score=score then @rank when @temp_score:=score then @rank:[email protected]+1 END `rank` from s_score s,(select @rank:=0,@temp_score:=NULL) q ORDER BY score desc
And rank times to skip
If there is a parallel ranking, the next ranking will be skipped automatically. For example, if there are two parallel first, 91 should become the third, and the ranking corresponds to the number of people.
SELECT name,score,rank from ( SELECT name ,score,@rank :=IF( @temp_score = score, @rank, @rank_incr ) `rank`,@rank_incr := @rank_incr + 1, @temp_score := score FROM s_score s,([email protected] := 0,@temp_rank := NULL,@rank_incr := 1 ) q ORDER BY score DESC) a
Using SQL window functions
The basic syntax of the window function is as follows:
Select sort function / aggregate function over (< partition by… > partition field order by sort field)
Note that there is a space after the over. This grammar is a little painful. I tried it more than a dozen times to write it successfully.
According to Wikipedia:Window functionAllows access to data in the record before and after the current record. Window function definition IframeOr a columnwindowWhere the current row has a given length around it and the calculation is performed across the dataset in the window. It can be understood that a window is a data set and a function is a method of calculating data.
Parton by is optional. If partition by is not used, the whole table is taken as a set. Finally, the sorting function is used to get the sorting number of each record according to the sorting sequence.
Sorting functions mainly include rank () and deny_ rank、row_ Number, their main differences:
- Rank (): sort the same field. If the fields are the same, they will be ranked side by side, and there will be a ranking gap.
- dense_ Rank (): sort the same field. If the fields are the same, they will be ranked side by side and ranked continuously
- row_ Number (): the ranking of the same field is related. Even if the same field appears, it will not be ranked in parallel
select name,score, RANK() over (ORDER BY score DESC) `rank`,ROW_NUMBER() over (order by score DESC) `row`, DENSE_RANK()over (ORDER BY score DESC) `dense` from s_score
The above is the implementation of sorting ranking. There are other implementation methods. Please add them in the comments.