mysql function select 赋值_数据分析面试之mysql的分组排序和变量赋值顺序

最新推荐文章于 2023-12-22 10:51:40 发布

weixin_39834406

最新推荐文章于 2023-12-22 10:51:40 发布

阅读量137

点赞数

学习过mysql的同学，大都体会过mysql的一个的痛点，那就是mysql没有支持分组排序的开窗函数。但是我们能够通过使用变量来达到这个目的。

我们先来创建一个人员年龄表(如下)，有个这样的需求是，根据不同性别进行分组排序他们的年龄，并得到序列号

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));

INSERT INTO person VALUES (1, 'Bob', 25, 'M');
INSERT INTO person VALUES (2, 'Jane', 20, 'F');
INSERT INTO person VALUES (3, 'Jack', 30, 'M');
INSERT INTO person VALUES (4, 'Bill', 32, 'M');
INSERT INTO person VALUES (5, 'Nick', 22, 'M');
INSERT INTO person VALUES (6, 'Kathy', 18, 'F');
INSERT INTO person VALUES (7, 'Steve', 36, 'M');
INSERT INTO person VALUES (8, 'Anne', 25, 'F');

我们先来得到想要的结果，然后来和大家解释下

select 
    first_name,
    gender,
    age ,
    rank
from
    (select  
        first_name,
        gender,
        age,
        @rank:=if(@gen=gender,@rank+1,1) rank,
        @gen:=gender
    from person,(select @rank:=0,@gen:=null) temp
    order by gender, age asc) b

结果如下：

一：mysql 变量解释

通过set 赋值变量

通过select 赋值变量

二：解释上面的分组排序代码

第一步先赋值变量

第二步使用IF条件进行分组

下面，我们不要太关注from person,(select @rank:=0,@gen:=null) temp，就是当作进行变量的初始化就好

   select  
        first_name,
        gender,
        age,
        @rank:=if(@gen=gender,@rank+1,1) rank,
        @gen:=gender
    from person,(select @rank:=0,@gen:=null) temp
    order by gender, age asc

和大家说下这个代码的流程a.第一步：变量赋值，是先运行from 后面的内容，以及排序，排序的目的是把 F、M放到各自的组中（这一点和我们原来的先select 后order 是不一样的，等下会有说明）此时@rank等于0,@gen等于nullb.第二步：开始进行select中的内容，会先进行第一行，运行@rank:=if(@gen=gender,@rank+1,1) rank，此时@gen是等于null的，而gender 是第一行的值，所以IF函数将会返回1，第一行的rank就会返回1，接着运行@gen:=gender ，此时的@gen会被赋值第一行的值第二行,
还是先运行@rank:=if(@gen=gender,@rank+1,1) rank，此时的@gen是等于gender，根据IF会返回@rank+1 然后赋值到@rank，直到遇到下一个不一样的gender，@rank 才会重新变成1

三：变量赋值顺序

我们通过看个例子来感受下

set @rownum:=0;
select 
        first_name,
        gender,
        age, @rownum as rownum
from person
where @rownum<1
order by first_name,least(0,@rownum:=@rownum+1);

在sql 语句中的执行顺序是 from 、where 、select 、order by
在这我们的疑问是先进行的order by 后进行的 select

如果是先进行的select 的话，rownum会输出0、1，而真实的结果是
rownnum是输出的1、2