在mysql中，根据用户在多行中的总积分对他们进行排名 [英] Rank users in mysql by their total points across multiple rows

问题描述

我对这个问题的描述非常相似.

I have very much similar kind of requirement as described in this question.

按等级对mysql中的用户进行排名

唯一的区别是我的数据.上面的问题有数据，其中表每个学生只有一行.但就我而言，这样的表格可能会为一个学生包含多行

The only difference is in my data. The above problem has the data where table has only row per student. But in my case there may be a possibility that table contains multiple rows for a single student like this

• 学生1分80
• 学生2分77.5
• 学生2分4.5
• 学生3分77
• 学生4分77

因此，现在应该基于用户的得分(总分)SUM计算排名.因此，在这种情况下，结果将会是.

So now rank should be calculated based on the SUM of points (total) that user has. So in this case result would be.

• 学生2排名1(82分)
• 学生1排名2(80分)
• 学生3(77分)排名3
• 学生4排名3(77分)

用于数据的SQL提琴

我尝试了上述问题的解决方案，但无法获得结果.任何帮助将不胜感激.

I tried couple of things with the solution of above question but couldn't get the result. Any help would be appreciated.

推荐答案

在我之前的答案中使用相同的查询，只是将表格Student更改为子查询以合并每个学生的所有记录

Using the same query in my previous answer just change the table student for a subquery to combine all records of every student

change [student er]  for 

(SELECT `id`, SUM(`points`) as `points`
 FROM students 
 GROUP BY `id`) er

SQL DEMO

select er.*,
       (@rank := if(@points = points, 
                    @rank, 
                    if(@points := points,    
                       @rank + 1, 
                       @rank + 1                       
                      )
                   )                  
       ) as ranking
from (SELECT `id`, SUM(`points`) as `points`
      FROM students 
      GROUP BY `id`) er cross join
     (select @rank := 0, @points := -1) params
order by points desc;

输出

| id | points | ranking |
|----|--------|---------|
|  5 |     91 |       1 |
|  6 |     81 |       2 |
|  1 |     80 |       3 |
|  2 |     78 |       4 |
|  3 |     78 |       4 |
|  4 |     77 |       5 |
|  7 |     66 |       6 |
|  8 |     15 |       7 |

这篇关于在mysql中，根据用户在多行中的总积分对他们进行排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！