mysql排序,数值一样同排名
set @curr_cnt:=0,@pre_cnt:=0,@rank :=0;
select a2, @curr_cnt:= cnt as cnt,
@rank:=if(@pre_cnt <> @curr_cnt,@rank+1,@rank) rank,@pre_cnt:=@curr_cnt
from (
SELECT a2,COUNT(*) cnt
from mm
where period='2019-01'
GROUP BY a2
ORDER BY cnt desc
) q;
普通排序:
SELECT p.a2,p.cnt,@ran:=@ran+1 as rownum
from (SELECT a2,COUNT(*) as cnt
from mm
where period='2019-01'
GROUP BY a2
ORDER BY cnt desc) p,(select @ran :=0)b;
更复杂排序,也是同成绩同排名
SELECT m.rownum,n.* FROM
(SELECT a.*,@rank :=@rank +@sart AS rownum ,@sart := a.num sart FROM
( SELECT nps, ROUND(nps) r3, COUNT(group_name) num FROM group_nps_ssi_score WHERE type ='3' AND group_name =? AND period =? GROUP BY ROUND(nps) ORDER BY ROUND(nps) DESC) a, ( SELECT @rank := 1 ) b, ( SELECT @sart := 0 ) c ) m,
( SELECT dealer_code, dealer_name,sample_size, ROUND( nps ) r3, recommend,accept,renegade FROM group_nps_ssi_score WHERE type = '3' AND group_name =? AND period =? ORDER BY ROUND( nps ) DESC ) n
WHERE m.r3 = n.r3 ORDER BY m.rownum, round( nps ) DESC