Shulou(Shulou.com)06/01 Report--

Today, the editor will share with you the relevant knowledge points about how to use rank () over, dense_rank () over and row_number () over in MySQL. The content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article. Let's take a look at it.

The answer to the above question if you do not use the function used this time is as follows, that is, if your MySQL cannot use the function in this article, you can replace it with the following syntax logic.

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 DESCrank () over (business logic)

Function: find out the ranking after the specified conditions, the same conditions, the same ranking, discontinuous ranking.

Description: for example, the ranking of students, using this function, the two places with the same scores are juxtaposed, and the next student vacates the position occupied. Namely: 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 ranking after the specified conditions, the same conditions, the same ranking, discontinuous ranking.

Note: the function is the same as rank () over, except that the dense_rank () over ranking is dense and continuous. For example, the ranking of students, using this function, the two places with the same scores are tied, and the next student goes on to the next place. Namely: 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 the ranking after the specified conditions, the same conditions are also different, the ranking is intermittent and discontinuous.

Note: this function does not need to consider whether it is juxtaposed or not, and it will be sorted continuously even if the values queried according to the conditions are the same. Namely: 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, not limited to ORDER BY, but also can be added with PARTITION BY.

# ranking SELECT id, name, score, class, row_number () over (PARTITION BY class ORDER BY score DESC) AS 'rank' FROM student by class

These are all the contents of the article "how to use rank () over, dense_rank () over and row_number () over in MySQL". Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.