mysql 实现 row_number() over(pattition by a order by b )

SELECT prod_province,prod_cnode,prod_balance,rank FROM
(
SELECT b.prod_province,b.prod_cnode,b.prod_balance,@rownum:=@rownum+1 ,
IF(@pdept=b.prod_province,@rank:=@rank+1,@rank:=1) AS rank, @pdept:=b.prod_province
FROM (
SELECT prod_province,prod_cnode,SUM(prod_balance) prod_balance
FROM t_prod_info
GROUP BY prod_province,prod_cnode
ORDER BY prod_province,prod_balance DESC
) b ,(SELECT @rownum :=0 , @pdept := '' ,@rank:=0) c
) result
HAVING rank <4 ;