今日分享
Setting goals is the first step in turning the invisible into the visible.
—— Tony Robbins
对现有的数据,根据某一字段进行排名,如果没有 RANK
函数,SQL
语句怎么写?接下来就分享一道面试题......更多精彩文章请关注公众号『Pythonnote』或者『全栈技术精选』
现在有一张表,保存着学生的相关信息(id
、名字以及分数),如下图所示。请写出 sql
语句查询排名第4的学生信息。
/* 进行排名,相同的分数同名次 */select *from (select name, score, (case when @p=score then @r when @p:=score then @r:=@r+1 end) as stu_rank from tb_student, (select @r:=0,@p:=NULL) r order by score desc) v_rankwhere v_rank.stu_rank=4;
3.分析
在讲解之前,先让我们创建一个测试数据库 testdb
,然后创建一张测试用表 tb_student
:
use testdb;
create table tb_student( id int auto_increment primary key, name varchar(10), score int);
然后按照图片中的信息进行构造数据:
insert into tb_student(name, score) values('小三', 1000);insert into tb_student(name, score) values('jack', 99);insert into tb_student(name, score) values('rose', 10);insert into tb_student(name, score) values('马蓉', 8);insert into tb_student(name, score) values('李小璐', 40);insert into tb_student(name, score) values('fuck', 300);insert into tb_student(name, score) values('李小璐2', 40);insert into tb_student(name, score) values('李小璐3', 40);
赋值号::=
条件语句:case when
1.首先需要根据分数进行排名(分数相同的同学,名次应该也是相同的,并列关系)
select name, score, (case when @p=score then @r when @p:=score then @r:=@r+1 end) as stu_rankfrom tb_student, (select @r:=0,@p:=NULL) as rorder by score desc;
2.然后再查出某一名次的学生
/* 进行排名,相同的分数应该是同名次 */select *from (select name, score, (case when @p=score then @r when @p:=score then @r:=@r+1 end) as stu_rank from tb_student, (select @r:=0,@p:=NULL) r order by score desc) v_rankwhere v_rank.stu_rank=4;