1.不按分组添加序号
在这里插入图片描述
1.第一种
select (@i := @i + 1) as rownum,b.* from table b, (SELECT @i := 0) as a order by region_id desc
1
2.第二种
set @i=0;
select @i:=@i+1 as rownum, b.* from table b order by region_id desc
1
2
上面两种方法原理就是,事先定义一个变量,通过变量的递加以及虚拟表的联查达到生成序号列的目的
2.按分组添加序号
在这里插入图片描述
select
-- rownum 判断 @now_region_id是否和当前的region_id一样,true:让 @i+=1 false:重置@i
(@i := case when @now_region_id=region_id then @i + 1 else 1 end ) rownum,
-- 设置 @now_region_id等于region_id
(@now_region_id:=region_id),
b.*,
from table b,
(SELECT @i := 0, @now_region_id:='') as a
order by region_id desc