mysql5.7实现row_number() over()

示例sql，此sql不能应用于生产，生成rowNumber可以参考。主要是没对country、region等进行group。

select * from
(select 
-- 定义变量 rn, year, month,变量以@开头。满足条件将变量rn+1
 @rn:= case when @year=b.year and @month = b.month then @rn+1 else 1 end as rn, b.sales_volume, b.country,b.region,
 -- 将变量@year等赋值
b.brand,b.kd_cbu,b.prod_mode, @year:=b.year as year, @month:=b.month as month from 
(select * 
from 
(SELECT
	case when country is null or country = '#N/A' then '其他' else country end as country,
  case when region is null or region = '#N/A' then '其他' else region end as region,
	YEAR,month,
	sum(sales_volume) as sales_volume,
	case when brand is null then '其他' else brand end as brand,
	kd_cbu,
	prod_mode
FROM
	omda_data_analysis
	where year = 2021
	GROUP BY year,	month,
	brand) as s_1 ORDER BY s_1.year,s_1.month, s_1.sales_volume desc) b
) a