mysql补充：局部变量

mysql补充：局部变量

我住永安当关注

2020.09.19 18:15:02字数 656阅读 125

mysql中各种类型变量的定义以及赋值使用
MySQL中实现rank排名查询

# 1、局部变量（即@开头的变量）
在一个会话内，只须初始化一次，之后在会话内都是对上一次计算的结果，就相当于在是这个会话内的全局变量。
mysql中局部变量不用事前申明，在用的时候直接用“@变量名”使用就可以了。
# 2、语法
初始值赋值：

set @num=1 
set @num:=1 

使用

select @num:=1
select @num:=字段名 from 表名 where 条件

注意：
使用set时可以用“=”或“:=”，但是使用select时必须用“:=赋值”

set @name = '';
select @name:=password from user limit 0,1;  #从数据表中获取一条记录password字段的值给@name变量。在执行后输出到查询结果集上面。

如果直接写：select @name:=password from user;

如果这个查询返回多个值的话，那@name变量的值就是最后一条记录的password字段的值 。

题目1：根据学生的年年龄进行排序，相同年龄的学生为一组，组内编号依次为1，2，3...
实现：一个字段内部的排序。
MySQL中row_number的实现
通常，用group by 可以实现一个字段的聚合，再结合order by进行排序。但是在一个字段内部不同的'值'再聚合并排序，就不是group by能够做到的了。
此处可以使用局部变量实现自增，给字段内不同value 使用case...when...进行条件判断后打上标签，也就实现了排序功能。
使用

set @row_num:=0,@customer_num:=0
select 
@row_num:=
    case when @customer_num=(year(now())-year(stu.sage)) then @row_num+1 else 1 end as num,
        @customer_num := (year(now())-year(stu.sage)) as age_n,
stu.sid,stu.sname
from student as stu
order by sid

 

 

运行结果：

image.png

 

具体运行过程成：

 

image.png

是否真的是这样有待验证，这个过程在mysql中应该是是一行一行进行的，否则这个判断没办法实现row＿num自增。并且根据order by sid后得到了想要的排序结果

另一种用户变量的初始赋值技巧：

select 
@row_num:=
    case when @customer_num=(year(now())-year(stu.sage)) then @row_num+1 else 1 end as num,
        @customer_num := (year(now())-year(stu.sage)) as age_n,
stu.sid,stu.sname
from student as stu,(select @row_num:=0,@customer_num:=0) as b
order by sid

得到相同的运行结果

 

image.png

题目2：按各科成绩进行排序，并显示排名， Score 重复时也继续排名
set @i:=0,@j:=null
select sid,cid,@i:=if(@i=@j,@i,@i+1) as stu_rank,@j:=score as stu_score
from sc
order by stu_score desc
方法一：case ...when...

set @row_num:=0,@stu_number:=0
select sc.sid,sc.cid,
    @row_num:=
        case @stu_number when sc.score then @row_num else @row_num+1 
        end as score_rank,
    @stu_number:=sc.score as stu_score
from sc
order by stu_score desc

另一种初始赋值形式也可以写成：

select sc.sid,sc.cid,
    @row_num:=
        case @stu_number when sc.score then @row_num else @row_num+1 
        end as score_rank,
    @stu_number:=sc.score as stu_score
from sc,(select @row_num:=0,@stu_number:=0) as a
order by stu_score desc

运行结果：

 

image.png

方法二：if(exp1,exp2,exp3)

#set @i:=0,@j:=null
select 
    sid,
    cid,
    @i:=if(@j=score,@i,@i+1) as stu_rank,
    @j:=score as stu_score
from sc
order by stu_score desc

另一种初始赋值形式也可以写成：

select 
    sid,
    cid,
    @i:=if(@j=score,@i,@i+1) as stu_rank,
    @j:=score as stu_score
from sc,(select @i:=0,@j:=null) as a

运行结果：

 

image.png

题目中的要求要简单些,不需要判断是否与上一行相等，一直继续排序即可。相当于根据成绩排名后添加行号。

select 
    sid,
    cid,
    @i:=@i+1 as stu_rank,
    score
from sc,(select @i:=0) as a
order by score desc

运行结果：

 

image.png