mysql中按分组添加序号

1.不按分组添加序号

1.第一种

select  (@i := @i + 1) as rownum,b.*  from    table b, (SELECT  @i := 0) as a    order  by   region_id desc

2.第二种

set @i=0;
select @i:=@i+1 as rownum, b.* from table b order  by   region_id desc

上面两种方法原理就是，事先定义一个变量，通过变量的递加以及虚拟表的联查达到生成序号列的目的

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