用简单程序替换 MySQL 窗口函数

  A 1 set @i1=null, @i2=0, @i3=0, @d1=null; 2 select curr_month, t1.province, curr_sales, sale_rank,

 

if(count>1, (sale_rank-1)/(count-1), 0) as `percent_rank`

from (select prev_month, curr_month, province,

@i2:=if(prev_month=curr_month,@i2+1,1) as `row_number`,

@i3:=if(prev_month<>curr_month, 1, if(prev_sales=curr_sales,

@i3, @i2)) as ‘sale_rank’, prev_sales, curr_sales

from (select @i1 as prev_month, @i1:=yearmonth as curr_month,

province, @d1 as prev_sales, @d1:=sales as curr_sales

from (select *

from detail

where yearmonth in (201601,201602)

order by yearmonth, sales desc

) t111

) t11

) t1

join

(select yearmonth, province, count(*) count

from detail

where yearmonth in (201601, 201602)

group by yearmonth

) t2