(蔡庐总结)mysql排名语句.pdf
文本预览下载声明
（蔡庐总结 ）mysql排名语句
mysql排排名名语语句句
说说明明：：排排名名最最主主要要解解决决的的问问题题，，就就是是分分数数相相同同的的情情况况。。如如果果积积分分相相同同，，必必须须考考 以以其其他他字字段段进进行行排排序序。。
这这样样才才能能保保证证固固定定的的排排名名顺顺序序。。
-- 创建student表
CREATE TABLE `student` (
`id` varchar(32) NOT NULL,
`name` varchar(255) EFAULT NULL,
`create_time` datetime EFAULT NULL,
`total_score` double EFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=Inno B EFAULT CHARSET=utf8;
-- 创建索引
alter table student
add index ts (total_score);
-- 插入测试数据
insert into student (
id,
name,
create_time,
total_score
)
select 123456789,我是当事人,2016-04-20 20:21:00,11.0 union
select replace(UUI (),-,),我勒个去,2016-04-20 20:22:00,21.0 union
select replace(UUI (),-,),测试的命,2016-04-20 10:23:00,24.0 union
select replace(UUI (),-,),开发,2016-04-21 20:24:00,23.0 union
select replace(UUI (),-,),产品,2016-04-23 20:25:00,11.0 union
select replace(UUI (),-,),牛人,2016-04-22 20:26:00,55.0 union
select replace(UUI (),-,),小白,2016-04-24 20:27:00,11.0 union
select replace(UUI (),-,),随便,2016-04-25 20:28:00,23.0 union
select replace(UUI (),-,),什么人,2016-04-19 20:28:00,11.0 union
select replace(UUI (),-,),排名,2016-04-15 20:24:00,11.0 union
select replace(UUI (),-,),所在排名,2016-04-26 20:29:00,32.0;
方方法法实实现现一一：：
-- 所所有有学学生生排排名名
select
s.*,
(select count(*)+1 from student p where p.total_scores.total_score or (p.total_score = s.total_score and p.create_time
s.create_time) ) as rank
from student s
order by rank;
-- 某某个个学学生生所所在在排排名名
select
s.*,
(select count(*)+1 from student p where p.total_scores.total_score or (p.total_score = s.total_score and p.create_time
s.create_time) ) as rank
from student s
where s.id = 123456789;
方方法法实实现现二二 ：
-- 所所有有学学生生排排名名
select
p.*,
@rank:=@rank+1 as rank from
(select * from student s order by s.total_score desc ,s.create_time desc ) p,
(SELECT @rank:=0) r
-- 某某个个学学生生所所在在排排名名
select t.* from (
select
p.*,
@rank:=@rank+1 as rank from
显示全部