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 ;