row_number() over(partition by ...)转换成mysql语句

一、Oracle语句：

select row_number() over (partition by goods_no,category_id order by price desc) as row_number,
       id,
       category_id,
       category,
       name,
       price,
       goods_no
from goods;

 

转换成mysql语句：

select IF(@per_no=g.goods_no and @cur_id=g.category_id,@r:=@r+1,@r:=1) as row_number,
       id,
       category_id,
       category,
       name,
       price,
       goods_no,
       @per_no:=g.goods_no,
       @cur_id:=g.category_id
from goods g,(select @per_no:=null,@cur_id:=null,@r:=0)tmp
order by goods_no,category_id,price desc;

二、Oracle语句：

select rownumber() over() as rownumber,t1.* from user t1;

转换成MySQL：

select @row_rank:=@row_rank+1 as rownumber,t1.* 
from user t1,(select @row_rank:=0)tmp;