Как обрабатывать ничьи при ранжировании результатов в MySQL?

Как обрабатывать связи при ранжировании результатов в запросе 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

Итак, представьте, что приведенный выше запрос выдает:

Students  Rank  Grades
=======================
Al         1     90
Amy        2     90
George     3     78
Bob        4     73
Mary       5     NULL
William    6     NULL

Несмотря на то, что у Эла и Эми одинаковая оценка, один из них оценивается выше, чем другой. Эми обворовали. Как мне сделать так, чтобы у Эми и Эла был одинаковый рейтинг, чтобы у них обоих был ранг 1. Кроме того, Уильям и Мэри не прошли тест. Они забрали класс и курили в комнате мальчика. Они должны быть привязаны к последнему месту.

Правильный рейтинг должен быть:

Students  Rank  Grades
========================
Al         1     90
Amy        1     90
George     2     78
Bob        3     73
Mary       4     NULL
William    4     NULL

Если у кого-то есть какие-либо советы, пожалуйста, дайте мне знать.


mysql sql window-functions rank
person Laxmidi    schedule 19.03.2010    source источник

Ответы (2)


arrow_upward
16
arrow_downward

EDIT: поддерживается MySQL 4.1+.

Использовать:

   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 это ВНУТРЕННЕЕ СОЕДИНЕНИЕ без каких-либо критериев) для объявления и использования переменной без использования отдельного оператора SET.

Вам нужно, чтобы COALESCE правильно обрабатывал NULL.

person OMG Ponies    schedule 19.03.2010
comment
Привет OMG Ponies, вы Терминатор MySQL!! Круто, большое спасибо за помощь. Я бы никогда не догадался. (Мои знания SQL являются базовыми, поэтому мне придется изучить ваш ответ, чтобы лучше понять его). Кроме того, мне нравится твой псевдоним. Еще раз СПАСИБО!!! -Лакшмиди - person Laxmidi; 19.03.2010
comment
Вы можете использовать перекрестное соединение (в MySQL это ВНУТРЕННЕЕ СОЕДИНЕНИЕ без каких-либо критериев) для объявления и использования переменной без использования отдельного оператора SET. Черт, я понятия не имел, что ты можешь это сделать. +1 - person heisenberg; 19.03.2010
comment
OMG Ponies - я опробовал ваше решение, оно сработало отлично. Но должен признаться, я до сих пор не совсем уверен, как это работает. Не могли бы вы добавить более подробную информацию о том, как ученики с одинаковыми оценками получают одинаковые баллы? Или если есть ссылка в другом месте, которая описывает эту технику. Спасибо! - person Brian Armstrong; 12.04.2011
comment
@Brian Armstrong: оператор CASE будет повторять значение rownum, когда переменная класса равна либо текущей строке, либо нулю. В противном случае значение ранга будет увеличиваться и отображаться для строки. Последний столбец в предложении SELECT устанавливает значение оценки в переменную для сравнения со следующим значением оценки. ORDER BY гарантирует, что оценки с самым высоким рангом будут иметь наименьший номер. - person OMG Ponies; 12.04.2011

arrow_upward
1
arrow_downward

Звучит как правило промежуточного программного обеспечения, которое было бы лучше выражено в коде, расположенном между базой данных и клиентом.

Если это невозможно, я бы порекомендовал хранимую процедуру в MySQL для выполнения запроса в том виде, в котором вы его написали, а затем изменить результаты с помощью курсора и массива.

person duffymo    schedule 19.03.2010
comment
Привет duffymo, Спасибо за ваш комментарий. Я ценю помощь. -Лакшмиди - person Laxmidi; 19.03.2010
comment
OMG Ponies - человек на этом. Какой потрясающий ответ. - person duffymo; 19.03.2010