sql server中的row_number在mysql中如何实现

sql server中的row_number在mysql中如何实现

row_number是一个很好用的排序函数，在mysql中没有，通过其他方法将其在mysql中实现。

原始数据

mysql的代码：
set @row=0;
set @mid=’’;
select a.goods_type,a.goods_name,a.price,
case
when rownum/num<0.2 then ‘低档’
when rownum/num>=0.2 and rownum/num<0.85 then ‘中档’
else ‘高档’ end
from (
select goods_type,goods_name,price,
case when @mid=goods_type then @row:=@row+1 else @row:=1 end rownum,@mid:=goods_type
from goods
order by goods_type,price
)a
join (
select goods_type,count(goods_name) as num
from goods
group by Goods_type
)b on a.goods_type=b.goods_type

实现结果：

其实通过row_number更方便：
select Goods_type,goods_name,price
,case when rownum/num<0.2 then ‘低档’
when rownum/num>=0.2 and rownum/num<0.85 then ‘中档’
else ‘高档’ end
from(
select Goods_type,goods_name,price,row_number() over(partition by Goods_type order by price) rank
from goods a
join (
select Goods_type,count(goods_name) as num
from goods
group by Goods_type
)b on a.Goods_type=b.Goods_type
)c