在MySQL中对结果进行排名时如何处理关系? [英] How do I Handle Ties When Ranking Results in MySQL?

问题描述

在mysql查询中对结果进行排名时，如何处理联系?在此示例中，我简化了表名和列，但它可以说明我的问题:

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:

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

即使Al和Amy的成绩相同，一个人的排名也比另一个高.艾米被偷走了.我如何才能使Amy和Al具有相同的排名，以使他们俩的排名都为1.而且，William和Mary没有参加考试.他们装束课，并在男孩的房间里吸烟.他们应该并列最后位置.

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.

正确的排名应该是:

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.

推荐答案

编辑:这是受MySQL 4.1+支持的

EDIT: This is MySQL 4.1+ supported

使用:

   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

您可以使用交叉联接(在MySQL中，是不带任何条件的INNER JOIN)来声明和使用变量，而无需使用单独的SET语句.

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.

您需要COALESCE来正确处理NULL.

You need the COALESCE to properly handle the NULLs.

这篇关于在MySQL中对结果进行排名时如何处理关系?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！