Shulou(Shulou.com)05/31 Report--

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!

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.