How does one handle ties when ranking results in a mysql query? I've simplified the table names and columns in this example, but it should illustrate my problem:
如何在mysql查询中对结果进行排名时如何处理关系?我在这个例子中简化了表名和列,但它应该说明我的问题:
SET @rank=0;
SELECT student_names.students,
@rank := @rank +1 AS rank,
scores.grades
FROM student_names
LEFT JOIN scores ON student_names.students = scores.students
ORDER BY scores.grades DESC
So imagine the the above query produces:
所以想象一下上面的查询产生:
Students Rank Grades
=======================
Al 1 90
Amy 2 90
George 3 78
Bob 4 73
Mary 5 NULL
William 6 NULL
Even though Al and Amy have the same grade, one is ranked higher than the other. Amy got ripped-off. How can I make it so that Amy and Al have the same ranking, so that they both have a rank of 1. Also, William and Mary didn't take the test. They bagged class and were smoking in the boy's room. They should be tied for last place.
虽然Al和Amy的成绩相同,但其中一个排名高于另一个。艾米被扯掉了。我怎样才能让艾米和艾尔拥有相同的排名,这样他们的排名都是1.另外,威廉和玛丽没有参加考试。他们上课并在男孩的房间里吸烟。他们应该并列最后一个位置。
The correct ranking should be:
正确的排名应该是:
Students Rank Grades
========================
Al 1 90
Amy 1 90
George 2 78
Bob 3 73
Mary 4 NULL
William 4 NULL
If anyone has any advice, please let me know.
如果有人有任何建议,请告诉我。
2 个解决方案
#1
16
EDIT: This is MySQL 4.1+ supported
编辑:这是MySQL 4.1+支持
Use:
SELECT st.name,
sc.grades,
CASE
WHEN @grade = COALESCE(sc.grades, 0) THEN @rownum
ELSE @rownum := @rownum + 1
END AS rank,
@grade := COALESCE(sc.grades, 0)
FROM STUDENTS st
LEFT JOIN SCORES sc ON sc.student_id = st.id
JOIN (SELECT @rownum := 0, @grade := NULL) r
ORDER BY sc.grades DESC
You can use a cross join (in MySQL, an INNER JOIN without any criteria) to declare and use a variable without using a separate SET
statement.
您可以使用交叉连接(在MySQL中,没有任何条件的INNER JOIN)来声明和使用变量,而无需使用单独的SET语句。
You need the COALESCE to properly handle the NULLs.
您需要COALESCE来正确处理NULL。
#2
1
Sounds like a middleware rule that would be better expressed in code that sat between the database and the client.
听起来像一个中间件规则,可以在数据库和客户端之间的代码中更好地表达。
If that's not possible, I'd recommend a stored procedure in MySQL to run the query as you wrote it and then modify the results using a cursor and an array.
如果那是不可能的,我建议MySQL中的存储过程在您编写时运行查询,然后使用游标和数组修改结果。
#1
16
EDIT: This is MySQL 4.1+ supported
编辑:这是MySQL 4.1+支持
Use:
SELECT st.name,
sc.grades,
CASE
WHEN @grade = COALESCE(sc.grades, 0) THEN @rownum
ELSE @rownum := @rownum + 1
END AS rank,
@grade := COALESCE(sc.grades, 0)
FROM STUDENTS st
LEFT JOIN SCORES sc ON sc.student_id = st.id
JOIN (SELECT @rownum := 0, @grade := NULL) r
ORDER BY sc.grades DESC
You can use a cross join (in MySQL, an INNER JOIN without any criteria) to declare and use a variable without using a separate SET
statement.
您可以使用交叉连接(在MySQL中,没有任何条件的INNER JOIN)来声明和使用变量,而无需使用单独的SET语句。
You need the COALESCE to properly handle the NULLs.
您需要COALESCE来正确处理NULL。
#2
1
Sounds like a middleware rule that would be better expressed in code that sat between the database and the client.
听起来像一个中间件规则,可以在数据库和客户端之间的代码中更好地表达。
If that's not possible, I'd recommend a stored procedure in MySQL to run the query as you wrote it and then modify the results using a cursor and an array.
如果那是不可能的,我建议MySQL中的存储过程在您编写时运行查询,然后使用游标和数组修改结果。