Mysql并列排序

本文主要是介绍Mysql并列排序，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

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

SELECTu.family_id,u.total,CASEWHEN @last_score = u.total THEN @rankWHEN @last_score := u.total THEN @rank := @rank + 1END  AS topFROM(SELECTcount(*) AS total,d.family_idFROMdynamic_info  aLEFT JOIN thumbs_up_log  b on a.id=b.dynamic_idLEFT JOIN `user`  c on c.user_id=a.user_idLEFT JOIN family_member d on d.user_id=c.user_idWHEREb.modify_time >= #{startTime}AND b.modify_time <=  #{endTime}AND b.`status` = 1AND c.delete_status=0AND a.delete_status=0AND a.publish_status=2GROUP BY d.family_id) u,(SELECT @rank := 0, @last_score := NULL) rORDER 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;

这篇关于Mysql并列排序的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---