根据分数动态查询排名

最新推荐文章于 2024-09-14 15:35:55 发布

思想羊

最新推荐文章于 2024-09-14 15:35:55 发布

阅读量531

点赞数 1

CC 4.0 BY-SA版权

分类专栏： mybatis

本文链接：https://blog.youkuaiyun.com/qq_44866462/article/details/89087937

mybatis 专栏收录该内容

3 篇文章

订阅专栏

根据分数动态查询排名费劲千辛万苦写出来的

< select id="selectScoreRankingAll" resultMap="BaseResultMap" >
          SELECT
              @rownum :=@rownum + 1 AS ranking,tt.web_emp_id AS eid,tt.real_name AS realName,tt.profile_photo AS   
   profilePhoto,tt.sumScore,tt.position
              FROM
                  (
                   SELECT
                  t.web_emp_id,t.real_name,t.profile_photo,t.sumScore,t.position,@rownum
   := 0
                  FROM
                      ( SELECT t_user.web_emp_id,t_user.real_name,t_user.profile_photo,IFNULL((SELECT SUM(score) AS sumScore FROM t_score WHERE   
   t_score.eid=t_user.web_emp_id),0) AS sumScore,t_position.name  AS   
   position
                      from t_score RIGHT JOIN t_user on t_score.eid=t_user.web_emp_id
                      LEFT JOIN t_user_position_releation
                      on t_user.un_id=t_user_position_releation.user_id
                      LEFT JOIN t_position
                      on t_user_position_releation.position_id=t_position.un_id
                      GROUP BY t_user.web_emp_id
                      ORDER BY sumScore DESC,t_user.real_name DESC) t
                  ORDER BY
                  t.sumScore DESC,t.real_name DESC
                  ) tt    < /select >