对系统排名中的差距进行排名 [英] Ranking system gaps in ranking output

问题描述

我有一个项目，该项目按得分对不同项目进行排名，虽然排名还可以，但是当得分相同时，它会显示出差距.

I have this project that it ranks different items by their scores, the ranking is okay but it shows gaps when there is a tied score.

这是查询:

SELECT bgycode, scc_bgyscoretotal, FIND_IN_SET( scc_bgyscoretotal, (
SELECT GROUP_CONCAT(DISTINCT scc_bgyscoretotal
ORDER BY scc_bgyscoretotal DESC ) FROM bgyprofile)
) AS rank
FROM bgyprofile

，它的输出是这样的:

有什么办法解决这个问题? 在此先感谢您的英语不好

any way to fix this? Thanks in advance sorry for the bad english

推荐答案

您基本上需要使用会话变量进行仿真.

You basically need Dense_Rank() like functionality (available in MySQL version >= 8.0). In older versions of MySQL, it can be emulated using Session Variables.

• 在派生表中，确定排名scc_bgyscoretotal的值(具有等级1的最大值，依此类推).首先，获取scc_bgyscoretotal的唯一值，然后使用

• In a Derived table, determine ranking of a scc_bgyscoretotal (highest value having rank 1 and so on). Firstly, get unique values of scc_bgyscoretotal, and then determine their ranking using Session Variables.
• Now, simply join these Derived table to the main table bgyprofile using scc_bgyscoretotal.

请尝试以下操作:

SELECT t2.bgycode, 
       t2.scc_bgyscoretotal, 
       dt2.`rank` 
FROM bgyprofile AS t2 
JOIN 
(
 SELECT dt1.scc_bgyscoretotal, 
        @rank_no := @rank_no + 1 AS `rank`  
 FROM 
 (
  SELECT t1.scc_bgyscoretotal 
  FROM bgyprofile AS t1 
  GROUP BY t1.scc_bgyscoretotal 
  ORDER BY t1.scc_bgyscoretotal DESC
 ) AS dt1 
 CROSS JOIN (SELECT @rank_no := 0) AS init1
) AS dt2 ON dt2.scc_bgyscoretotal = t2.scc_bgyscoretotal 

这篇关于对系统排名中的差距进行排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！