mysql - 有没有一种简单的方法可以在 mysql 上调整成绩

Question

我有排名成绩

SELECT   Name, Grade,
         @rank_count := @rank_count + (Grade < @prev_value) rank,
         @prev_value := Grade avg
FROM     GradeTable, (SELECT @prev_value := NULL, @rank_count := 1) init
ORDER BY avg DESC

我的平均水平很低

Alex  6.44
Carl  6.33
Greg  6.21
Tom   6.06

我只想把最高分变成 10 等等

Alex  10
Carl  9.89
Greg  9.77
Tom   9.62

我不想要正态分布复杂的东西，我可以做一个简单的曲线升级，还是我必须用复杂的正态分布公式来计算曲线数？

Answer 1

排名似乎与这个问题无关。您可以通过获取最大值并进行一些算术来缩放数字:

SELECT   Name, Grade, 10 * Grade / maxgrade as scaledGrade,
         @rank_count := @rank_count + (10 * Grade / maxgrade  < @prev_value) as rank,
         @prev_value := 10 * Grade / maxgrade  as avg
FROM     GradeTable CROSS JOIN
         (SELECT @prev_value := NULL, @rank_count := 1) init CROSS JOIN
         (SELECT MAX(Grade) as maxgrade FROM GradeTable) gt
ORDER BY avg DESC;

编辑:

根据示例数据，您似乎不想缩放等级，只是为了提高等级:

SELECT   Name, Grade, (Grade + 10 - maxgrade) as scaledGrade,
         @rank_count := @rank_count + ((Grade + 10 - maxgrade) < @prev_value) as rank,
         @prev_value := (Grade + 10 - maxgrade) as avg
FROM     GradeTable CROSS JOIN
         (SELECT @prev_value := NULL, @rank_count := 1) init CROSS JOIN
         (SELECT MAX(Grade) as maxgrade FROM GradeTable) gt
ORDER BY avg DESC;