MYSQL同分同名排名刷新问题

最新推荐文章于 2023-02-28 15:00:43 发布

豆瓣号

最新推荐文章于 2023-02-28 15:00:43 发布

阅读量811

点赞数

文章标签： mysql 同分同名排名刷新

本文链接：https://blog.csdn.net/sinat_38220981/article/details/87970108

版权

最近使用MySQL跑项目报表排名出现一个bug,报表对用户积分按从高到低进行排序，积分相同的名次也相同。其他情况下排名正常显示，但是当有多个用户排名并列第一时则会初先，第一个人排名为1，后面积分相同的并列第2名。如下图：
在这里插入图片描述
三个用户按分类积分排名，本来应该都是第一名，但是实际上却只有一个第一，两个第二。我先找到存储过程里刷新排名的sql：

select nbr,target_nbr,rank_value,CASE WHEN @curGroup=substring(organ_nbr,1,12) and @rowtotal=obj.rank_value THEN @row 
  WHEN @curGroup=substring(organ_nbr,1,12) and @rowtotal:=obj.rank_value THEN @row := @row+1 
  WHEN @curGroup=substring(organ_nbr,1,12) and @rowtotal=0 THEN @row := @row+1
  WHEN @curGroup<>substring(organ_nbr,1,12)  THEN @row:=1
	END urank,
	@curGroup:=substring(organ_nbr,1,12) FROM ( SELECT
        nbr,
        organ_nbr,
					target_nbr,
					target_name,
					IFNULL(rank,0) rank,
        IFNULL(rank_value,0) rank_value
    FROM `t03_rank`
   where rank_parent_type = 'PERSON_SORT_BONUS_YEAR_RANK' and rank_type = 'VILLAGERANK' and rank_year ='2019'
    ORDER BY
       substring(organ_nbr,1,12) DESC, rank_value DESC
) AS obj,
(SELECT @row := 0 ,@rowtotal := 0,@curGroup='') r ORDER BY urank ASC

这里先大致介绍下这个刷新过程，先在t03_rank表查询要刷新的数据，因为表里的用户分属不同的机构，所以排名还要按机构来，在同一机构的用户按分类积分以同分同名的规则刷新排名。当第一名只有一个时这个排名刷新一直没有问题。出现这个问题很明显是由于当用户机构不同时，执行case中WHEN @curGroup<>substring(organ_nbr,1,12) THEN @row:=1语句，此时该条记录排名赋值为1，当该机构内同分的用户来刷新排名时由于机构编号相同，会首先执行WHEN @curGroup=substring(organ_nbr,1,6) and @rowtotal:=obj.rank_value THEN @row := @row+1语句，即先赋值，然后排名加1，此时存储排名的@row已经是1，再加1就会变成2，所以第二个同分用户排名就变成了2 ，当第三个同名用户近来刷新时，由于机构编号相同，且排名比较的值@rowtotal也相同，就会执行WHEN @curGroup=substring(organ_nbr,1,12) and @rowtotal=obj.rank_value THEN @row语句。此时@row与第二个用户相同，所以也是2。所以问题就处在第一次机构编号不同时的情况，我最先用了个头痛医头的办法，写法如下：

select c.*,IF(c.urank=0,1,c.urank) rank from (SELECT nbr,target_nbr,rank_value,
	CASE
		WHEN @curGroup=substring(organ_nbr,1,12) and @rowtotal=obj.rank_value THEN @row 
		WHEN @curGroup=substring(organ_nbr,1,12) and @rowtotal:=obj.rank_value THEN @row := @row+1 
  WHEN @curGroup=substring(organ_nbr,1,12) and @rowtotal=0 THEN @row := @row+1
		WHEN @curGroup<>substring(organ_nbr,1,12)  THEN @row:=0
	END urank,
	@curGroup:=substring(organ_nbr,1,12) FROM (
    SELECT
        nbr,
        organ_nbr,
					target_nbr,
					target_name,
					IFNULL(rank,0) rank,
        IFNULL(rank_value,0) rank_value
    FROM
        `t03_rank`
   where rank_parent_type = 'PERSON_SORT_BONUS_YEAR_RANK' and rank_type = 'CITYRANK' and rank_year ='2019'
    ORDER BY
       substring(organ_nbr,1,12) DESC, rank_value DESC
) AS obj,
(SELECT @row := 0 ,@rowtotal := 0,@curGroup='') r ORDER BY urank ASC) c

即将WHEN @curGroup<>substring(organ_nbr,1,12) THEN @row:=1 改成@curGroup<>substring(organ_nbr,1,12) THEN @row:=0，当机构不同时该用户排名值先设成0，这样的话第二个用户刷新会在0基础上加1，就是1，第三个用户由于机构和积分相同，同名同分，所有排名也是1，但此时第一个用户排名却是0，所有外面再加一层判断IF(c.urank=0,1,c.urank) ，将0改成1，这一样三个用户最后排名就全是1了，看起来没啥问题，可如果排名第一名只有一个时就又会有问题。所以说该方法不可行。
后来分析发现，其实要解决这个问题，只需在执行判断机构不同的时候就赋值就可以解决问题。所以我把sql这么写，最后成功解决问题。

select nbr,target_nbr,rank_value,CASE WHEN @curGroup=substring(organ_nbr,1,12) and @rowtotal=obj.rank_value THEN @row 
  WHEN @curGroup=substring(organ_nbr,1,12) and @rowtotal:=obj.rank_value THEN @row := @row+1 
  WHEN @curGroup=substring(organ_nbr,1,12) and @rowtotal=0 THEN @row := @row+1
  WHEN @curGroup<>substring(organ_nbr,1,12) and @rowtotal:=obj.rank_value THEN @row:=1
	END urank,
	@curGroup:=substring(organ_nbr,1,12) FROM ( SELECT
        nbr,
        organ_nbr,
					target_nbr,
					target_name,
					IFNULL(rank,0) rank,
        IFNULL(rank_value,0) rank_value
    FROM `t03_rank`
   where rank_parent_type = 'PERSON_SORT_BONUS_YEAR_RANK' and rank_type = 'VILLAGERANK' and rank_year ='2019'
    ORDER BY
       substring(organ_nbr,1,12) DESC, rank_value DESC
) AS obj,
(SELECT @row := 0 ,@rowtotal := 0,@curGroup='') r ORDER BY urank ASC

WHEN @curGroup<>substring(organ_nbr,1,12) and @rowtotal:=obj.rank_value THEN @row:=1，此处完成赋值。当机构编号相同时下一条记录就不会执行 WHEN @curGroup=substring(organ_nbr,1,12) and @rowtotal:=obj.rank_value THEN @row := @row+1 ，而去直接执行 WHEN @curGroup=substring(organ_nbr,1,12) and @rowtotal=obj.rank_value THEN @row，这样无论何种情况都会按case条件执行。