SELECT cid ,ip,quan,rank
FROM (
SELECT b.cid ,b.ip,quan,@rownum:=@rownum+1 ,
IF(@pdept=b.cid,@rank:=@rank+1,@rank:=1) AS rank,
@pdept:=b.cid
FROM (
SELECT cid,ip,count(num) quan
FROM schema.table_name
where cid in ('aid1','aid2')
group by cid ,ip_address
order by cid desc,count(num) desc
) b ,
(SELECT @rownum :=0 , @pdept := '' ,@rank:=0) c
) result
HAVING rank <4 ;
FROM (
SELECT b.cid ,b.ip,quan,@rownum:=@rownum+1 ,
IF(@pdept=b.cid,@rank:=@rank+1,@rank:=1) AS rank,
@pdept:=b.cid
FROM (
SELECT cid,ip,count(num) quan
FROM schema.table_name
where cid in ('aid1','aid2')
group by cid ,ip_address
order by cid desc,count(num) desc
) b ,
(SELECT @rownum :=0 , @pdept := '' ,@rank:=0) c
) result
HAVING rank <4 ;