MySQL實現排名並查詢指定用戶排名功能，併列排名功能 表結構： CREATE TABLE test.testsort ( id int(11) NOT NULL AUTO_INCREMENT, uid int(11) DEFAULT 0 COMMENT '用戶id', score decimal( ...
MySQL實現排名並查詢指定用戶排名功能，併列排名功能
表結構： CREATE TABLE test.testsort ( id int(11) NOT NULL AUTO_INCREMENT, uid int(11) DEFAULT 0 COMMENT '用戶id', score decimal(10, 2) DEFAULT 0.00 COMMENT '分數', PRIMARY KEY (id) ) ENGINE = INNODB AUTO_INCREMENT = 1 CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT = '測試排序' ROW_FORMAT = DYNAMIC; 思路：可以先排序，再對結果進行編號；也可以先查詢結果，再排序編號。 說明： @rownum := @rownum + 1 中 := 是賦值的作用，這句話的意思是先執行@rownum + 1，然後把值賦給@rownum； (SELECT @rownum := 0) r 這句話的意思是設置rownum欄位的初始值為0，即編號從1開始。 實現排名： 方法一： SELECT t.*, @rownum := @rownum + 1 AS rownum FROM (SELECT @rownum := 0) r, (SELECT * FROM testsort ORDER BY score DESC) AS t; 方法二： SELECT t.*, @rownum := @rownum + 1 AS rownum FROM (SELECT @rownum := 0) r, testsort AS t ORDER BY t.score DESC; 結果：
查看指定用戶排名： 方法一： SELECT b.* FROM ( SELECT t.*, @rownum := @rownum + 1 AS rownum FROM (SELECT @rownum := 0) r, (SELECT * FROM testsort ORDER BY score DESC) AS t ) AS b WHERE b.uid = 222; 方法二: SELECT b.* from ( SELECT t.*, @rownum := @rownum + 1 AS rownum FROM (SELECT @rownum := 0) r, testsort AS t ORDER BY t.score DESC ) as b where b.uid = 222; 結果：
實現併列排名（相同分數排名相同）： 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
查詢指定用戶併列排名:
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 = 222;
