MySQL 5.7 开窗 累计求和 分组排序

MySQL 低版本没有开窗函数
开发过程中碰到了需要分组排序和累计求和的需求

-- 累计求和
select 
t1.*,
@rn:= case when @q = typ then @rn+num else num end as amt,
@q:= typ
from 
	(select	'a' as typ,	1 as num
	union all select	'a',	2
	union all select	'a',	3
	union all select	'a',	4
	union all select	'b',	6
	union all select	'b',	7
	union all select	'b',	8
	union all select	'b',	9
	) t1
left join (select @q:=0,@rn := 0) t0 on 1=1

	select t.*
	,@rn := CASE WHEN @q = a THEN @rn + 1 ELSE 1 END AS rn1
	,@q := t.a aa
	from(
		select 3 a,2 b,1 c
		union select 'p' a, 3 b,2 c
		union select 1 a, 6 b,3 c
		union select 1 a, 5 b,4 c
		union select 2 a,2 b,5 c
		union select 2 a, 3 b,6 c
		union select 2 a, 6 b,7 c
		union select 2 a, 5 b,8 c
	) t ,(select @q:=0,@rn := 1) t0