Mysql 查询排名

（供自己学习）

查询全部的排名

       SELECT
    obj_new.user_id as userId,
    obj_new.study_time as studyTime,
    obj_new.rownum as rank
FROM
    (
        SELECT
            obj.user_id,
            obj.study_time,
            @rownum := @rownum + 1 AS num_tmp,
            @incrnum := CASE
        WHEN @rowtotal = obj.study_time THEN
            @incrnum
        WHEN @rowtotal := obj.study_time THEN
            @rownum
        END AS rownum
        FROM
            (
                SELECT
                    user_id,
                    study_time
                FROM
                    `t_live_study`
                ORDER BY
                    CAST(study_time AS UNSIGNED) DESC
            ) AS obj,
            (
                SELECT
                    @rownum := 0 ,@rowtotal := NULL ,@incrnum := 0
            ) r
    ) AS obj_new

结果如下

查询单个的排名

       SELECT
    obj_new.user_id as userId,
    obj_new.study_time as studyTime,
    obj_new.rownum as rank
FROM
    (
        SELECT
            obj.user_id,
            obj.study_time,
            @rownum := @rownum + 1 AS num_tmp,
            @incrnum := CASE
        WHEN @rowtotal = obj.study_time THEN
            @incrnum
        WHEN @rowtotal := obj.study_time THEN
            @rownum
        END AS rownum
        FROM
            (
                SELECT
                    user_id,
                    study_time
                FROM
                    `t_live_study`
                ORDER BY
                    CAST(study_time AS UNSIGNED) DESC
            ) AS obj,
            (
                SELECT
                    @rownum := 0 ,@rowtotal := NULL ,@incrnum := 0
            ) r
    ) AS obj_new

		where user_id = #{userId}