非实时排行榜设计 mysql_mysql非占位排名_MySQL

最新推荐文章于 2022-11-03 23:56:03 发布

肆卿

最新推荐文章于 2022-11-03 23:56:03 发布

阅读量50

点赞数

文章标签：非实时排行榜设计 mysql

本文链接：https://blog.csdn.net/weixin_33435783/article/details/113901908

版权

找了好久的mysql排名解决办法，排名结果：1,2,2,3,4,5,5

看sql吧，相信可以看的懂。是从stackoverflow中找的。

SELECT name,clazz,user_id,class_id,xn,scoreSum,awardedMarks,totalPoint,CASE WHEN @prev_value = totalPoint THEN @rank_count WHEN @prev_value := totalPoint THEN @rank_count := @rank_count + 1END AS rank FROM (SELECT au.`NAME` AS NAME,CONCAT(vc.`levelname`,vc.`gradename`,vc.`classname`) AS clazz, cs.user_id,cs.class_id,cs.`xn`,SUM(cs.`project_score`) AS scoreSum,IFNULL(ca.`marks`,0) awardedMarks, (SUM(cs.`project_score`)+IFNULL(ca.`marks`,0)) AS totalPointFROM ct_score cs LEFT JOIN ct_project cp ON cs.`project_id`=cp.`project_id` LEFT JOIN aq_user au ON cs.`user_id`=au.`USER_ID` LEFT JOIN vw_xj_e_class vc ON cs.`class_id`=vc.`classid` LEFT JOIN `ct_awardedmarks` ca ON ca.`class_id`=cs.`class_id` AND ca.`user_id`=cs.`user_id`,(SELECT @prev_value := NULL) f,(SELECT @rank_count := 0) mWHERE cp.`participant`='Personal' GROUP BY cs.`user_id`) t ORDER BY totalPoint DESC;