如何在 MySQL 中执行分组排名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/532878/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to perform grouped ranking in MySQL
提问by achinda99
So I have a table as follows:
所以我有一个表如下：
ID_STUDENT | ID_CLASS | GRADE
-----------------------------
1 | 1 | 90
1 | 2 | 80
2 | 1 | 99
3 | 1 | 80
4 | 1 | 70
5 | 2 | 78
6 | 2 | 90
6 | 3 | 50
7 | 3 | 90
I need to then group, sort and order them to give:
然后我需要对它们进行分组、排序和排序以给出：
ID_STUDENT | ID_CLASS | GRADE | RANK
------------------------------------
2 | 1 | 99 | 1
1 | 1 | 90 | 2
3 | 1 | 80 | 3
4 | 1 | 70 | 4
6 | 2 | 90 | 1
1 | 2 | 80 | 2
5 | 2 | 78 | 3
7 | 3 | 90 | 1
6 | 3 | 50 | 2
Now I know that you can use a temp variable to rank, like here, but how do I do it for a grouped set? Thanks for any insight!
现在我知道您可以使用临时变量进行排名，就像这里一样，但是我如何为分组集执行此操作？感谢您的任何见解！
回答by Quassnoi
SELECT id_student, id_class, grade,
@student:=CASE WHEN @class <> id_class THEN 0 ELSE @student+1 END AS rn,
@class:=id_class AS clset
FROM
(SELECT @student:= -1) s,
(SELECT @class:= -1) c,
(SELECT *
FROM mytable
ORDER BY id_class, id_student
) t
This works in a very plain way:
这以非常简单的方式工作：
- Initial query is ordered by
id_classfirst,id_studentsecond. @studentand@classare initialized to-1@classis used to test if the next set is entered. If the previous value of theid_class(which is stored in@class) is not equal to the current value (which is stored inid_class), the@studentis zeroed. Otherwise is is incremented.@classis assigned with the new value ofid_class, and it will be used in test on step 3 at the next row.
- 初始查询按
id_class第一、id_student第二排序。 @student并@class初始化为-1@class用于测试是否输入了下一组。如果 的先前值id_class（存储在 中@class）不等于当前值（存储在 中id_class），则将@student归零。否则是递增的。@class被分配了新的值id_class，它将在下一行的第 3 步的测试中使用。
回答by Nicolas Payart
There is a problem with Quassnoi's solution (marked as best answer).
Quassnoi 的解决方案有问题（标记为最佳答案）。
I have the same problematic (i.e. simulating SQL Window Function in MySQL) and I used to implement Quassnoi's solution, using user-defined variables to store previous row value...
我有同样的问题（即在 MySQL 中模拟 SQL 窗口函数），我曾经实现 Quassnoi 的解决方案，使用用户定义的变量来存储前一行值...
But, maybe after a MySQL upgrade or whatever, my query did not work anymore. This is because the order of evaluation of the fields in SELECT is not guaranteed. @class assignment could be evaluated before @student assignment, even if it is placed after in the SELECT.
但是，也许在 MySQL 升级或其他什么之后，我的查询不再有效。这是因为无法保证 SELECT 中字段的评估顺序。@class 赋值可以在 @student 赋值之前进行评估，即使它被放置在 SELECT 之后。
This is mentionned in MySQL documentation as follows :
这在 MySQL 文档中提到如下：
As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement; in addition, this order is not guaranteed to be the same between releases of the MySQL Server.
作为一般规则，您永远不应为用户变量赋值并在同一语句中读取该值。您可能会得到预期的结果，但这并不能保证。涉及用户变量的表达式的求值顺序未定义，可能会根据给定语句中包含的元素而改变；此外，不保证此顺序在 MySQL 服务器的版本之间是相同的。
source : http://dev.mysql.com/doc/refman/5.5/en/user-variables.html
来源：http: //dev.mysql.com/doc/refman/5.5/en/user-variables.html
Finally I have used a trick like that to be sure to assign @class AFTER reading it :
最后，我使用了这样的技巧来确保在阅读后分配@class：
SELECT id_student, id_class, grade,
@student:=CASE WHEN @class <> id_class THEN concat(left(@class:=id_class, 0), 0) ELSE @student+1 END AS rn
FROM
(SELECT @student:= -1) s,
(SELECT @class:= -1) c,
(SELECT *
FROM mytable
ORDER BY id_class, grade desc
) t
Using left() function is just used to set @class variable. Then, concatenate the result of left() (equal to NULL) to the expected result is transparent.
使用 left() 函数只是用来设置@class 变量。然后，将 left() 的结果（等于 NULL）连接到预期结果是透明的。
Not very elegant but it works!
不是很优雅，但它的工作原理！
回答by Jon Armstrong - Xgc
SELECT g1.student_id
, g1.class_id
, g1.grade
, COUNT(*) AS rank
FROM grades AS g1
JOIN grades AS g2
ON (g2.grade, g2.student_id) >= (g1.grade, g1.student_id)
AND g1.class_id = g2.class_id
GROUP BY g1.student_id
, g1.class_id
, g1.grade
ORDER BY g1.class_id
, rank
;
Result:
结果：
+------------+----------+-------+------+
| student_id | class_id | grade | rank |
+------------+----------+-------+------+
| 2 | 1 | 99 | 1 |
| 1 | 1 | 90 | 2 |
| 3 | 1 | 80 | 3 |
| 4 | 1 | 70 | 4 |
| 6 | 2 | 90 | 1 |
| 1 | 2 | 80 | 2 |
| 5 | 2 | 78 | 3 |
| 7 | 3 | 90 | 1 |
| 6 | 3 | 50 | 2 |
+------------+----------+-------+------+
回答by achinda99
Modified from above, this works but its more complex than I think it needs to be:
从上面修改，这有效，但它比我认为需要的更复杂：
SELECT ID_STUDENT, ID_CLASS, GRADE, RANK
FROM
(SELECT ID_STUDENT, ID_CLASS, GRADE,
@student:=CASE WHEN @class <> id_class THEN 1 ELSE @student+1 END AS RANK,
@class:=id_class AS CLASS
FROM
(SELECT @student:= 0) AS s,
(SELECT @class:= 0) AS c,
(SELECT *
FROM Students
ORDER BY ID_CLASS, GRADE DESC
) AS temp
) AS temp2
回答by achinda99
I did some searching, found this articleto come up with this solution:
我做了一些搜索，发现这篇文章提出了这个解决方案：
SELECT S2.*,
FIND_IN_SET(
S2.GRADE
, (
SELECT GROUP_CONCAT(GRADE ORDER BY GRADE DESC)
FROM Students S1
WHERE S1.ID_CLASS = S2.ID_CLASS
)
) AS RANK
FROM Students S2 ORDER BY ID_CLASS, GRADE DESC;
Any thoughts on which is better?
关于哪个更好的任何想法？
回答by reeslabree
SELECT ID_STUDENT, ID_CLASS, GRADE, RANK() OVER(
PARTITION BY ID_CLASS
ORDER BY GRADE ASC) AS 'Rank'
FROM table
ORDER BY ID_CLASS;
I had a similar problem for a homework assignment, found that MySQL (can't speak for any other RDBMS) has a partition argument for its RANK() method. Don't see why it wouldn't work for this problem.
我在家庭作业中遇到了类似的问题，发现 MySQL（不能代表任何其他 RDBMS）的 RANK() 方法有一个分区参数。不明白为什么它不能解决这个问题。
