MySQL实现排名查询、并指定用户查看排名

表结构

CREATE TABLE `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 DEFAULT CHARSET=utf8;

INSERT INTO `tts`.`testsort` (`id`, `uid`, `score`)
VALUES
	('1', '1', '1.00'),
	('2', '1', '2.00');

INSERT INTO `tts`.`testsort` (`id`, `uid`, `score`)
VALUES
	;

INSERT INTO `tts`.`testsort` (`id`, `uid`, `score`)
VALUES
	('3', '1', '3.00');

INSERT INTO `tts`.`testsort` (`id`, `uid`, `score`)
VALUES
	('4', '6', '4.00');

INSERT INTO `tts`.`testsort` (`id`, `uid`, `score`)
VALUES
	('5', '8', '5.00');

INSERT INTO `tts`.`testsort` (`id`, `uid`, `score`)
VALUES
	('6', '9', '6.00');

INSERT INTO `tts`.`testsort` (`id`, `uid`, `score`)
VALUES
	('7', '1', '7.00');

INSERT INTO `tts`.`testsort` (`id`, `uid`, `score`)
VALUES
	('8', '1', '7.00');

INSERT INTO `tts`.`testsort` (`id`, `uid`, `score`)
VALUES
	('9', '1', '8.00');

INSERT INTO `tts`.`testsort` (`id`, `uid`, `score`)
VALUES
	('10', '1', '8.00');

INSERT INTO `tts`.`testsort` (`id`, `uid`, `score`)
VALUES
	('11', '1', '9.00');

INSERT INTO `tts`.`testsort` (`id`, `uid`, `score`)
VALUES
	('12', '1', '9.00');

INSERT INTO `tts`.`testsort` (`id`, `uid`, `score`)
VALUES
	('13', '1', '1.00');

INSERT INTO `tts`.`testsort` (`id`, `uid`, `score`)
VALUES
	('14', '1', '2.00');

INSERT INTO `tts`.`testsort` (`id`, `uid`, `score`)
VALUES
	('15', '1', '2.00');

INSERT INTO `tts`.`testsort` (`id`, `uid`, `score`)
VALUES
	('16', '1', '3.00');

实现排名

思路：可以先排序，再对结果进行编号；也可以先查询结果，再排序编号。

说明： @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;

+----+------+-------+--------+
| id | uid  | score | rownum |
+----+------+-------+--------+
| 12 |    1 |  9.00 |      1 |
| 11 |    1 |  9.00 |      2 |
|  9 |    1 |  8.00 |      3 |
| 10 |    1 |  8.00 |      4 |
|  7 |    1 |  7.00 |      5 |
|  8 |    1 |  7.00 |      6 |
|  6 |    9 |  6.00 |      7 |
|  5 |    8 |  5.00 |      8 |
|  4 |    6 |  4.00 |      9 |
| 16 |    1 |  3.00 |     10 |
|  3 |    1 |  3.00 |     11 |
|  2 |    1 |  2.00 |     12 |
| 14 |    1 |  2.00 |     13 |
| 15 |    1 |  2.00 |     14 |
| 13 |    1 |  1.00 |     15 |
|  1 |    1 |  1.00 |     16 |
+----+------+-------+--------+
16 rows in set (0.00 sec)

方法二

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

+----+------+-------+--------+
| id | uid  | score | rownum |
+----+------+-------+--------+
| 12 |    1 |  9.00 |      1 |
| 11 |    1 |  9.00 |      2 |
|  9 |    1 |  8.00 |      3 |
| 10 |    1 |  8.00 |      4 |
|  7 |    1 |  7.00 |      5 |
|  8 |    1 |  7.00 |      6 |
|  6 |    9 |  6.00 |      7 |
|  5 |    8 |  5.00 |      8 |
|  4 |    6 |  4.00 |      9 |
| 16 |    1 |  3.00 |     10 |
|  3 |    1 |  3.00 |     11 |
|  2 |    1 |  2.00 |     12 |
| 14 |    1 |  2.00 |     13 |
| 15 |    1 |  2.00 |     14 |
| 13 |    1 |  1.00 |     15 |
|  1 |    1 |  1.00 |     16 |
+----+------+-------+--------+
16 rows in set (0.00 sec)

查看指定用户排名 方法一

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 = 9;

+----+------+-------+--------+
| id | uid  | score | rownum |
+----+------+-------+--------+
|  6 |    9 |  6.00 |      7 |
+----+------+-------+--------+
1 row in set (0.00 sec)

方法二

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 = 8;

+----+------+-------+--------+
| id | uid  | score | rownum |
+----+------+-------+--------+
|  5 |    8 |  5.00 |      8 |
+----+------+-------+--------+
1 row in set (0.00 sec)

实现从指定用户uid为 1,6,8,9 中获取 uid 为 6 的排名

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

+----+------+-------+--------+
| id | uid  | score | rownum |
+----+------+-------+--------+
|  4 |    6 |  4.00 |      9 |
+----+------+-------+--------+
1 row in set (0.00 sec)