Mysql 不使用窗口函数实现分组排序& rank 别名问题

Mysql 不使用窗口函数实现分组排序

变量

定义和修改

方法1

set @变量名：=值；

set @name="bob";
set @name="jojo"; #重复赋值会更改为后的值

方法2

select @变量名：=值；

select @name:="bob";
select @age:=18; 
select @gender:="男";
select @gender:="adsg"; 

方法3

注意：要求查询的结果只能是一个值，如果是多个是不行的

select 查询的结果 into @变量名 from 表；

select @name:="bob";
select @name into @namebob;
select @namebob;

变量的计算

set @m:=3;
set @n:=2;
select @res=@m+@n;
select @m+@n into @num;
select @num ;--值为5

sql语句的执行顺序

先插入person表举例

create table person(
    id int ,
    first_name varchar(20),
    age int ,
    gender char(1)
);

insert into person(id, first_name, age, gender) values (1,'Bob',25,'M'),
                                                       (2,'Jane',20,'F'),
                                                       (3,'Jack',30,'M'),
                                                       (4,'Bill',32,'M'),
                                                       (5,'Nick',22,'M'),
                                                       (6,'Kathy',18,'F'),
                                                       (7,'Steve',39,'M'),
                                                       (8,'Anne',25,'F');

插入后结果如下

正常是先查询后排序

变量则是先排序后查询

select gender as man
from person
order by man  --结果执行正常表示是先查询然后再排序

set @rownum:=0;
select 
	first_name,
	gender,
	@rownum
from person 
where @rownum<=1
order by first_name,@rownum:=@rownum+1 #此处重新赋值

–least(0,@rownum:@rownum+1) 表示@rownum自增，然后与0做比较，显示最小值
– 如果是先执行查询后执行排序：会直接给@rownum赋值为0，查询结果中会有0值
–如果先执行排序后执行查询：则查询不出0值 ，因为在排序中已经自增长为1了

结果证明是先排序之后再进行的查询

inner join的简写

select * from a
inner join b
on a.id=b.id
--可以简写为,实际上市ab表做了笛卡尔积（a b的所有记录进行关联），然后where做条件筛选
select * from a，b
where a.id=b.id 

实现分组排序

#第一步先进行排序
select
id,
first_name,
gender,
age
from person
order by gender,age asc;

第二步：

select 
	id ,
	first_name,
	age,
	gender,
	rank
	from (
		select 
			id ,
			first_name,
			age,
			gender,
			@rank:=if(@gender=gender,@rank+1,1) as rank, #先判断gender的情况，然后再赋值为@rank
			@gender:=gender #赋值@gender
			from person,(select @rank:=0,@gender:=null) temp #表示笛卡尔积
			order by gender,age) a 
	where rank=1 #对结果进行筛选

查询结果如下：

解析：

–语句先判断@gender为null的时候判断第一行的gender值F与之不相等，所以输出@rank结果为1，且赋值了当前的gender值给@gender
–接着判断当前的gender值F与@gender值F相等，输出结果@rank+1=2
–接着判断当前的gender值F与@gender值F相等，输出结果@rank+1=3
–接着判断当前的gender值M与@gender值F不相等，输出@rank结果为1，且赋值了当前的gender值M给@gender
–接着判断当前的gender值M与@gender值M相等，输出结果@rank+1=2
–依次循环得到按照分组排序的效果

版本问题导致的if和rank列问题

如果是Mysql 8.0已上，已经不支持if ，且rank不能做为列名使用，

此时可以使用case when 代替 if，rank列名替换为其他的，语句如下

select 
	id ,
	first_name,
	age,
	gender,
	rownumber #别名替换
	from (
		select 
			id ,
			first_name,
			age,
			gender,
			#@rank=if(@gender=gender,@rank+1,1), #先判断gender的情况，然后再赋值为@rank
      @rank:=(case when @gender=gender then @rank+1 else 1 end) as rownumber,
			@gender:=gender #赋值@gender
			from person,(select @rank:=0,@gender:='') temp #，表示笛卡尔积
			order by gender,age) a
	where rownumber=1 #对结果进行筛选

同时，高版本的Mysql可以支持开窗函数，语句更加的简单便捷,

但实测8.0.23不支持

select 
	id ,
	first_name,
	age,
	gender,
	row_number() over (partition by gender order by age) as rownumber #row_number函数更加便捷
	from 
	person
	

本篇blog来源于根据b站up视频[https://www.bilibili.com/video/BV13i4y187SE?from=search&seid=13681091615578612777&spm_id_from=333.337.0.0]