202108061448 - mysql分组排序

drop table tmp_mytest ;
create table tmp_mytest (
id int ,
type varchar(10)
) ;
insert into tmp_mytest values(1, '1');
insert into tmp_mytest values(2, '1');
insert into tmp_mytest values(3, '1');
insert into tmp_mytest values(4, '1');
insert into tmp_mytest values(6, '2');
insert into tmp_mytest values(7, '2');
insert into tmp_mytest values(8, '2');
insert into tmp_mytest values(5, '2');

-- mysql 分组 按id排序
SELECT
	-- 判断 当前变量@type 和 a.type是否相同，相同则变量@r+1 ; 不同@r 置零
    @r:= case when @type=a.type  then @r+1 else 1 end as rowNum,
    -- 变量@type 重新赋值
    @type:=a.type as type,
    a.id
from 
    tmp_mytest a , (select @r:=0 ,@type:='') b 
		order by a.id asc 
;