周海汉/文 2012.9.11
mysql 不提供排名函数，所以需自己去实现。 排序先用mapreduce进行，但对于相同成绩的，其排名应该一样。而mapreduce由于没有先后关系的数据，所以没法做这工作。可以在应用程序中将数据循环读出，再判断是否分数相等，如果相等，则其名次相等。也可以在mysql 5.0以后的版本中，采用存储过程来实现。
需求中还需要单独取一个用户资料时，也得到其正确的排名数据。所以客户端临时排名不合适。服务器端临时排名也不合适。必须将排好名的数据写在数据库里。
准备 先创建表： mysql> desc mysort; +———+————-+——+—–+———+—————-+ | Field | Type | Null | Key | Default | Extra | +———+————-+——+—–+———+—————-+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | score | int(11) | YES | | NULL | | | rank | int(11) | YES | | NULL | | | caldate | varchar(20) | YES | | NULL | | | userid | int(11) | YES | | NULL | | +———+————-+——+—–+———+—————-+ 6 rows in set (0.00 sec)
CREATE TABLE `mysort` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`score` int(11) DEFAULT NULL,
`rank` int(11) DEFAULT NULL,
`caldate` varchar(20) DEFAULT NULL,
`userid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
插入数据 mysql> select * from mysort; +—-+——+——-+——+—————+——–+ | id | name | score | rank | caldate | userid | +—-+——+——-+——+—————+——–+ | 1 | a1 | 10 | 1 | 2012-09-10_24 | 151 | | 2 | a2 | 8 | 2 | 2012-09-10_24 | 131 | | 3 | a3 | 8 | 3 | 2012-09-10_24 | 12 | | 4 | a4 | 1 | 6 | 2012-09-10_24 | 11 | | 5 | a5 | 2 | 4 | 2012-09-10_24 | 211 | | 6 | a6 | 2 | 5 | 2012-09-10_24 | 212 | +—-+——+——-+——+—————+——–+ 可见其排名是正确的，唯一有问题的地方是相同分数排名不一样，用户会觉得不公平。
目标要排名后成为： +—-+——+——-+——+—————+——–+ | id | name | score | rank | caldate | userid | +—-+——+——-+——+—————+——–+ | 1 | a1 | 10 | 1 | 2012-09-10_24 | 151 | | 2 | a2 | 8 | 2 | 2012-09-10_24 | 131 | | 3 | a3 | 8 | 2 | 2012-09-10_24 | 12 | | 5 | a5 | 2 | 4 | 2012-09-10_24 | 211 | | 6 | a6 | 2 | 4 | 2012-09-10_24 | 212 | | 4 | a4 | 1 | 6 | 2012-09-10_24 | 11 | +—-+——+——-+——+—————+——–+
rank 的排名是一致的。这样保证相同分数score的人排名是一个。
单纯排名，可以有重复 对于不用更新数据库的方式，或者更新到临时表中的方式，最简单：
mysql> set @rank:=0;
mysql> select @rank:=@rank+1 as rank,id,name,score from mysort order by score desc;
+------+----+------+-------+
| rank | id | name | score |
+------+----+------+-------+
| 1 | 1 | a1 | 10 |
| 2 | 2 | a2 | 8 |
| 3 | 3 | a3 | 8 |
| 4 | 5 | a5 | 2 |
| 5 | 6 | a6 | 2 |
| 6 | 4 | a4 | 1 |
+------+----+------+-------+
rank是排过名后的，只是对分数相同的排名有先后，而且没有更新到表中。这对数据量少，只是显示，是一种最简便的排名方法。
另外一种排名，有select子句，所以效率较差
mysql> select * from (select (select count(id)+1 from mysort where score>a.score) as arank,a.name, a.score from mysort a) b order by b.arank;
+-------+------+-------+
| arank | name | score |
+-------+------+-------+
| 1 | a1 | 10 |
| 2 | a2 | 8 |
| 2 | a3 | 8 |
| 4 | a5 | 2 |
| 4 | a6 | 2 |
| 6 | a4 | 1 |
+-------+------+-------+
6 rows in set (0.00 sec)
但这个排名是满足要求的，只是没有写到表中，如果可以将整个数据导到另一个表，也是一种可行的办法。
第三种，用存储过程。 在mysql 5.0以上的版本，支持存储过程。 本代码是mysql存储过程实例，将表和时间做成了参数。
CREATE PROCEDURE p(in tname varchar(100),in cdate varchar(20))
BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE auid INT;
DECLARE ascore,arank INT;
DECLARE cur1 CURSOR FOR SELECT rank,userid,score FROM myview;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
drop view if exists myview;
SET @last_score =0;
SET @last_rank =0;
SET @last_uid =0;
SET @myuid=0;
SET @myrank=0;
SET @cmdc=concat("create view myview as SELECT rank,userid,score FROM ", tname," WHERE caldate='",cdate, "' ORDER BY rank" );
PREPARE pcursor FROM @cmdc;
EXECUTE pcursor;
DEALLOCATE PREPARE pcursor;
SET @cmdu = concat("update ",tname," set rank=? where userid=? ");
PREPARE pupdate FROM @cmdu;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO arank,auid,ascore;
IF done THEN
LEAVE read_loop;
END IF;
IF ascore = @last_score THEN
select auid,@last_rank;
IF @myrank=0 THEN
set @myrank:=@last_rank;
END IF;
set @myuid:=auid;
EXECUTE pupdate USING @myrank,@myuid;
ELSE
set @myrank:=0;
END IF;
SELECT arank,auid,ascore INTO @last_rank,@last_uid,@last_score;
END LOOP;
DEALLOCATE PREPARE pupdate;
CLOSE cur1;
END;
这里要注意一个陷阱，就是更新数据后，游标并不能得到新数据。
本存储过程用到如下几个知识点： 1.存储过程参数 2.表名做为存储过程的参数传入 3.游标使用，因为返回多行数据，需要针对每一行进行处理，所以用游标。 4.视图。游标不支持表名做为参数，所以创建了一个视图view 5.语句组建，采用prepare和execute及字符串处理concat函数。 6.局部变量和session变量使用，赋值和判断。set 赋值可以用=或:=;select 赋值必须用:=;@变量是session变量，不需声明。局部变量需声明。 7.if 语句 8.循环 9.如何判断数据读取结束，也可以用这句：DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET bOVER=TRUE;SQLSTATE ‘02000’就是结束。
调用 在mysql命令行下，先设置结束符： mysql> delimiter // 再输入edit 在vi中输入以上存储过程，保存。 调用：
mysql> delimiter //
mysql> edit
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> call p('mysort','2012-09-10_24');
-> //
Query OK, 0 rows affected (0.05 sec)
mysql> select * from mysort order by rank;
-> //
+----+------+-------+------+---------------+--------+
| id | name | score | rank | caldate | userid |
+----+------+-------+------+---------------+--------+
| 1 | a1 | 10 | 1 | 2012-09-10_24 | 151 |
| 2 | a2 | 8 | 2 | 2012-09-10_24 | 131 |
| 3 | a3 | 8 | 2 | 2012-09-10_24 | 12 |
| 5 | a5 | 2 | 4 | 2012-09-10_24 | 211 |
| 6 | a6 | 2 | 4 | 2012-09-10_24 | 212 |
| 4 | a4 | 1 | 6 | 2012-09-10_24 | 11 |
+----+------+-------+------+---------------+--------+
6 rows in set (0.00 sec)
存储过程操作 最后，如果要查看有哪些存储过程列表：
mysql> show procedure status;
如果要查看存储过程内容：
mysql> show create procedure p;
如果要更新存储过程，可以先删除，再创建。
mysql> drop procedure p;
p是我的存储过程名字。
mysql存储过程调试 我没用什么客户端ide，我直接select相关变量输出。
参考 排名方法讨论： http://www.ericyue.info/archive/mysql-seek-rankings
mysql变量使用总结 http://www.cnblogs.com/wangtao_20/archive/2011/02/21/1959734.html
如非注明转载, 均为原创. 本站遵循知识共享CC协议,转载请注明来源
