对系统排名中的差距进行排名 | 对系统排名中的差距进行排名

本文介绍了对系统排名中的差距进行排名的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

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

推荐答案

您基本上需要 Dense_Rank() 之类的功能(在MySQL 版本> = 8.0 中可用).在 MySQL的较旧版本中，可以使用会话变量进行仿真.

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的唯一值，然后使用会话变量.
现在，只需使用scc_bgyscoretotal将这些派生表联接到主表bgyprofile中即可.

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

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