Java修道之路:MySQL排名

mysql没有oracle那种排名函数,就只有自己实现,要么用sql,要么用java。

前几天研究了下sql的写法,贴出来,怕忘了可以瞄上一眼。

这里用到了mysql的自定义变量,写法如 @rank := 0,就是设置变量rank初始值为0。

为了测试sql,要有数据,用存储过程添加了1万条数据,花了大概13秒钟吧。

在公司测试的是10万条数据,花了110多秒钟吧,具体记不得了…

好了,下面开始贴代码:

1,随机生成1万条数据的sql,表只有两个字段:id和score,id自增,score随机0~100

-- 创建存储过程
drop procedure if exists proc;
create procedure proc() 
begin 
	declare i int;
	set i = 0;
	while i < 10000 do 
		insert into score values (null, format(rand() * 100, 0));
		set i = i + 1;
	end while;
end;

-- 执行存储过程
call proc();

2,排序有三种形式:

序列连续不重复,即从1~10000挨个排列,这个很简单,只需要排序加上行号就好了。

序列连续重复,即score相同的为并列名次,假如为并列第一名,那么下一名还是第二名。

序列不连续重复,即相同的为并列名次,假如为并列第一名,有3个,那么下一名将是第四名。

开始贴代码:

连续不重复:

-- 连续不重复排名
select T.*, @row := @row + 1 as row
from 
	(select * from score order by score desc, id) as T, 
	(select @row := 0) as ROW

连续重复:

-- 连续重复排名
select 
	BASE.id, 
	BASE.score, 
	-- 如果score等于上一行的score那么名次和上一行相同,否则+1
	if (
		BASE.score = (
			select LASTROW.score from (
				select T.*, @row := @row + 1 as row
				from 
					(select * from score order by score desc, id) as T, 
					(select @row := 0) as ROW
			) as LASTROW
			where LASTROW.row = BASE.row - 1 
		),
		@rank,
		@rank := @rank + 1
	) as rank 
from 
	(
		select T.*, @row := @row + 1 as row
		from 
			(select * from score order by score desc, id) as T, 
			(select @row := 0) as ROW
	) as BASE, 
	(select @rank := 0) as RANK

不连续重复:

-- 不连续重复排名
select 
	BASE.id, 
	BASE.score, 
	-- 如果score等于上一行的score那么名次和上一行相同,
	-- 否则等于连续不重复排序出来的名次
	if (
		BASE.score = (
			select LASTROW.score from (
				select T.*, @row := @row + 1 as row
				from 
					(select * from score order by score desc, id) as T, 
					(select @row := 0) as ROW
			) as LASTROW
			where LASTROW.row = BASE.row - 1 
		),
		-- 如果是相等的score, 那么rank就是BASE中该score的第一个row
		-- (不等的时候会对@rank进行赋值,就是赋值为第一个row)
		@rank,
		-- 如果不是相等的,那么rank就是BASE中的第一个row
		@rank := BASE.row
	) as rank 
from 
	(
		select T.*, @row := @row + 1 as row
		from 
			(select * from score order by score desc, id) as T, 
			(select @row := 0) as ROW
	) as BASE, 
	(select @rank := 0) as RANK

最后这个排名,执行时间大概在100+毫秒,10万条数据大概在500+毫秒,不知道和用java写排名算法比起来会怎样?

过几天再用java写下排名,统计下时间,算法已经想到了,就等啥时候写了…

本文《Java修道之路:MySQL排名》来自 www.juwends.com ,欢迎转载或CV操作,但请注明出处,谢谢!