How MySQL uses variables to achieve various sorts

This article mainly introduces how MySQL uses variables to achieve a variety of sorting, has a certain reference value, friends in need can refer to. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

Core code

-- next I'll demonstrate the implementation of sorting in MySQL-- Test data CREATE TABLE tb (score INT); INSERT tb SELECT 5 UNION ALL SELECT 4 UNION ALL SELECT 4 UNION ALL SELECT 4 UNION ALL SELECT 3 UNION ALL SELECT 2 UNION ALL SELECT1;--1.row_number sort SET @ row_number = 0 position select @ row_number: = @ row_number+1 AS row_number,score FROM tb ORDER BY score DESC +-+-+ | row_number | score | +-+-+ | 1 | 5 | 2 | 4 | 3 | 4 | 4 | 4 | 5 | 3 | 6 | 2 | 7 | 1 | +- -2.dense_rank sort SET @ dense_rank = 0 @ prev_score = NULL SELECT @ dense_rank: = IF (@ prev_score=score,@dense_rank,@dense_rank+1) AS decnse_rank, @ prev_score: = score AS score FROM tb ORDER BY score DESC +-+-+ | decnse_rank | score | +-+-+ | 1 | 5 | 2 | 4 | 2 | 4 | 2 | 4 | 3 | 3 | 4 | 4 | 5 | 1 | +- Sort SET @ row=0 of-+-- 3.rank style @ rank=0,@prev_score=NULL SELECT @ row:=@row+1 AS ROW, @ rank:=IF (@ prev_score=score,@rank,@row) AS rank,@ prev_score:=score AS scoreFROM tb ORDER BY score DESC +-+ | ROW | rank | score | +-+ | 1 | 1 | 5 | 2 | 2 | 4 | 3 | 2 | 4 | 4 | 2 | 4 | 5 | 5 | 6 | 6 | 2 | 2 | 7 | 7 | 1 | +- -+ Thank you for reading this article carefully. I hope it is helpful for everyone to share how MySQL uses variables to achieve a variety of ranking content. At the same time, I also hope that you will support it, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!