How to use rank() over, dense_rank() over and row_number() over in MySQL

For the above question, if you do not use the function used this time, the answer is as follows. In other words, if your MySQL cannot use the function in this article, you can pass the following grammatical logic for replacement.

SELECT t1.Score as Score, ( SELECT COUNT(DISTINCT t2.Score) FROM Scores t2 WHERE t2.Score >= t1.Score ) AS `Rank` 
FROM Scores t1 ORDER BY t1.Score DESC

rank() over (business logic)

Function: Ranking after finding out the specified conditions. If the conditions are the same, the rankings will be the same, and the rankings will be discontinuous.

This function can be used for student ranking, where two students with the same grades will be tied, and the next student will vacate his due position. That is: 1 1 3 4 5 5 7

SELECT id, name, score, rank() over(ORDER BY score DESC) AS 'rank' FROM student

##dense_rank() over (business logic)

Function: Find out the specified Those after the conditions are ranked. If the conditions are the same, the rankings are the same. The rankings are discontinuous.

Note: It has the same effect as rank() over, the difference is that dense_rank() over ranking is dense and continuous. Use this function to determine a student's ranking. In the event of a tie, two students will be tied for the same ranking, and the next ranked student will be determined based on the next ranking. That is: 1 1 2 3 4 5 5 6

SELECT id, name, score, dense_rank() over(ORDER BY score DESC) AS 'rank' FROM student

row_number() over (business logic)

Function: Find out Ranking is performed after specifying conditions. If the conditions are the same, the rankings will be different, and the rankings will be discontinuous.

Even if the queried values ​​are the same, this function will sort them continuously without considering whether they are parallel. That is: 1 2 3 4 5 6

SELECT id, name, score, row_number() over(ORDER BY score DESC) AS 'rank' FROM student

Postscript

The business logic in the function can be complex and is not limited to ORDER BY, you can also add PARTITION BY.

### 分班级排名
SELECT id, name, score, class, row_number() over(PARTITION BY class ORDER BY score DESC) AS 'rank' 
FROM student

The above is the detailed content of How to use rank() over, dense_rank() over and row_number() over in MySQL. For more information, please follow other related articles on the PHP Chinese website!