mysql排名

转载自思心思危http://www.cnblogs.com/zengguowang/p/5541431.html

 

一、sql1｛不管数据相同与否，排名依次排序（1,2,3,4,5,6,7.....）｝

SELECT
    obj.user_id,
　　 obj.score,
　　@rownum := @rownum + 1 AS rownum
FROM
    (
        SELECT
            user_id,
            score
        FROM
            `sql_rank`
        ORDER BY
            score DESC
    ) AS obj,
    (SELECT @rownum := 0) r

二、sql2｛只要数据有相同的排名就一样，排名依次排序（1,2,2,3,3,4,5.....）｝

SELECT
    obj.user_id,
    obj.score,
CASE
WHEN @rowtotal = obj.score THEN  @rownum
WHEN @rowtotal := obj.score THEN @rownum :=@rownum + 1
WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1
END AS rownum
FROM
    (
        SELECT
            user_id,
            score
        FROM
            `sql_rank`
        ORDER BY
            score DESC
    ) AS obj,
    (SELECT @rownum := 0 ,@rowtotal := NULL) r

三、sql2｛只要数据有相同的排名就一样，但是相同排名也占位，排名依次排序（1,2,2,4,5,5,7.....）｝　　

　　　此时需呀再增加一个变量，来记录排序的号码（自增）

SELECT
    obj_new.user_id,
    obj_new.score,
    obj_new.rownum
FROM
    (
        SELECT
            obj.user_id,
            obj.score,
            @rownum := @rownum + 1 AS num_tmp,
            @incrnum := CASE
        WHEN @rowtotal = obj.score THEN
            @incrnum
        WHEN @rowtotal := obj.score THEN
            @rownum
        END AS rownum
        FROM
            (
                SELECT
                    user_id,
                    score
                FROM
                    `sql_rank`
                ORDER BY
                    score DESC
            ) AS obj,
            (
                SELECT
                    @rownum := 0 ,@rowtotal := NULL ,@incrnum := 0
            ) r
    ) AS obj_new