Mysql并列排序

mysql并列排序 排序相同 排名相同

SELECT
            u.family_id,
            u.total,
            CASE
            WHEN @last_score = u.total THEN @rank
            WHEN @last_score := u.total THEN @rank := @rank + 1
            END  AS top
        FROM
        (
        SELECT
            count(*) AS total,
            d.family_id
        FROM
            dynamic_info  a
            LEFT JOIN thumbs_up_log  b on a.id=b.dynamic_id
            LEFT JOIN `user`  c on c.user_id=a.user_id
            LEFT JOIN family_member d on d.user_id=c.user_id
        WHERE
            b.modify_time >= #{startTime}
            AND b.modify_time <=  #{endTime}
            AND b.`status` = 1
            AND c.delete_status=0
            AND a.delete_status=0
            AND a.publish_status=2
            GROUP BY d.family_id
        ) u,
        (SELECT @rank := 0, @last_score := NULL) r
        ORDER BY u.total DESC limit 100;

排名并列 跳级N位

SELECT
family_id,
@curRank := IF(@prevRank = u.total, @curRank, @incRank) AS top,
@incRank := @incRank + 1,
@prevRank := u.total,
total
FROM (
SELECT
count(*) AS total,
d.family_id
FROM
dynamic_info a
LEFT JOIN thumbs_up_log b on a.id=b.dynamic_id
LEFT JOIN user c on c.user_id=a.user_id
LEFT JOIN family_member d on d.user_id=c.user_id
WHERE
b.status = 1
AND c.delete_status=0
AND a.delete_status=0
AND a.publish_status=2
GROUP BY d.family_id
) u,
(SELECT @curRank :=0, @prevRank := NULL, @incRank := 1) r
ORDER BY u.total DESC LIMIT 100;