一、不管数据相同与否，排名依次排序（1,2,3,4,5,6,7.....）
SELECT a.USER_ID, a.QUANTITY, @rownum := @rownum + 1 AS RANK FROM ( SELECT USER_ID, COUNT(*) AS QUANTITY FROM TEST GROUP BY USER_ID ORDER BY QUANTITY ) AS a, (SELECT @rownum := 0) r
二、只要数据有相同的排名就一样，排名依次排序（1,2,2,3,3,4,5.....）
SELECT a.USER_ID, a.QUANTITY, CASE WHEN @rowtotal = a.QUANTITY THEN @rownum WHEN @rowtotal := a.QUANTITY THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN @rownum :=@rownum + 1 END AS RANK FROM ( SELECT USER_ID, COUNT(*) AS QUANTITY FROM TEST GROUP BY USER_ID ORDER BY QUANTITY ) AS a, (SELECT @rownum := 0 ,@rowtotal := NULL) r
三、只要数据有相同的排名就一样，但是相同排名也占位，排名依次排序（1,2,2,4,5,5,7.....）
SELECT new.USER_ID, new.QUANTITY, new.RANK FROM ( SELECT a.USER_ID, a.QUANTITY, @rownum := @rownum + 1 AS num_tmp, @incrnum := CASE WHEN @rowtotal = a.QUANTITY THEN @incrnum WHEN @rowtotal := a.QUANTITY THEN @rownum END AS RANK FROM ( SELECT USER_ID, COUNT(*) AS QUANTITY FROM TEST GROUP BY USER_ID ORDER BY QUANTITY ) AS a, ( SELECT @rownum := 0 ,@rowtotal := NULL ,@incrnum := 0 ) r ) AS new
