How to realize the function of ranking and querying the ranking of specified users in MYSQL

This article mainly introduces MYSQL how to achieve ranking and query designated user ranking function, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor with you to understand.

Table structure:

CREATE TABLE test.testsort (id int (11) NOT NULL AUTO_INCREMENT, uid int (11) DEFAULT 0 COMMENT 'user id', score decimal (10,2) DEFAULT 0.00 COMMENT' score, PRIMARY KEY (id) ENGINE = INNODB AUTO_INCREMENT = 1 CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT = 'test sort' ROW_FORMAT = DYNAMIC

Idea: you can sort the results first, then number the results, or you can query the results first, and then sort the numbers.

Description:

@ rownum: = @ rownum + 1: = is the function of assignment, which means to execute @ rownum + 1 first, and then assign the value to @ rownum

(SELECT @ rownum: = 0) r means to set the initial value of the rownum field to 0, that is, the number starts at 1.

Achieve ranking:

Method 1:

SELECT T. FROM testsort ORDER BY score DESC, @ rownum: = @ rownum + 1 AS rownum FROM (SELECT @ rownum: = 0) r, (SELECT * FROM testsort ORDER BY score DESC) AS t

Method 2:

SELECT T. rownum, @ rownum: = @ rownum + 1 AS rownum FROM (SELECT @ rownum: = 0) r, testsort AS t ORDER BY t.score DESC

Results:

View the ranking of the specified users:

Method 1:

SELECT b.* FROM (SELECT. FROM testsort ORDER BY score DESC) AS, @ rownum: = @ rownum + 1 AS rownum FROM (SELECT @ rownum: = 0) r, (SELECT * FROM testsort ORDER BY score DESC) AS t) AS b WHERE b.uid

Method 2:

SELECT b.* from (SELECT T. from, @ rownum: = @ rownum + 1 AS rownum FROM (SELECT @ rownum: = 0) r, testsort AS t ORDER BY t.score DESC) as b where b.uid = 2222

Results:

Achieve juxtaposition ranking (same score and same rank):

SELECT obj.uid, obj.score, CASE WHEN @ rowtotal = obj.score THEN @ rownum WHEN @ rowtotal: = obj.score THEN @ rownum: = @ rownum + 1 WHEN @ rowtotal = 0 THEN @ rownum: = @ rownum + 1 END AS rownum FROM (SELECT uid, score FROM testsort ORDER BY score DESC) AS obj, (SELECT @ rownum: = 0, @ rowtotal: = NULL) r

Query specified users juxtaposed ranking:

SELECT total.* FROM (SELECT obj.uid, obj.score, CASE WHEN @ rowtotal = obj.score THEN @ rownum WHEN @ rowtotal: = obj.score THEN @ rownum: = @ rownum + 1 WHEN @ rowtotal = 0 THEN @ rownum: = @ rownum + 1 END AS rownum FROM (SELECT uid, score FROM testsort ORDER BY score DESC) AS obj, (SELECT @ rownum: = 0, @ rowtotal: = NULL) r) AS total WHERE total.uid Thank you for reading this article carefully. I hope the article "how to achieve ranking and query the ranking function of designated users in MYSQL" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!