Mysql实现ROW_NUMBER() OVER()

**

Mysql实现ROW_NUMBER() OVER()

**
#1.将要进行分组的列进行order by(这个menu排序必须要这样做)
select userid,menu,score from test_domain.wxc order by menu asc ,score desc ;

#2.先判断pdept变量是否等于menu，如果相等rank变量相等，否则rank变量为1(所以在这之前必须要将menu进行排序)，然后把当前menu赋值给pdept变量，
select H.userid,H.menu,H.score,@rownum:=@rownum+1 rownum,
if(@pdept=H.menu,@rank:=@rank+1,@rank:=1) as rank,
@pdept:=H.menu
from (
select userid,menu,score from test_domain.wxc order by menu asc ,score desc
) H ,(select @rownum :=0 , @pdept := null ,@rank:=0) a
;

#3.取出相应的字段即可
select userid,menu,score,rank,rownum from
(
select H.userid,H.menu,H.score,@rownum:=@rownum+1 rownum,
if(@pdept=H.menu,@rank:=@rank+1,@rank:=1) as rank,
@pdept:=H.menu
from (
select userid,menu,score from test_domain.wxc order by menu asc ,score desc
) H ,(select @rownum :=0 , @pdept := null ,@rank:=0) a ) result ;