Присвоение ранга в создании mySQL и VIEW

У меня есть таблица под названием турнирная таблица, в которой есть очки, разница мячей и голы, забитые каждой командой. Вот структура таблицы и данные.

CREATE TABLE standings (
  team_id int(3) unsigned NOT NULL AUTO_INCREMENT,
  points int(2) unsigned DEFAULT 0,
  goal_difference int(2) unsigned DEFAULT 0,
  goals_for int(2) unsigned DEFAULT 0,
  PRIMARY KEY (team_id)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

insert into standings(team_id,points,goal_difference,goals_for) values (1,20,2,17);
insert into standings(team_id,points,goal_difference,goals_for) values (2,14,8,15);
insert into standings(team_id,points,goal_difference,goals_for) values (3,9,2,11);
insert into standings(team_id,points,goal_difference,goals_for) values (4,14,10,12);
insert into standings(team_id,points,goal_difference,goals_for) values (5,17,10,19);
insert into standings(team_id,points,goal_difference,goals_for) values (6,5,-11,7);
insert into standings(team_id,points,goal_difference,goals_for) values (7,14,10,10);
insert into standings(team_id,points,goal_difference,goals_for) values (8,9,2,14);
insert into standings(team_id,points,goal_difference,goals_for) values (9,12,1,10);
insert into standings(team_id,points,goal_difference,goals_for) values (10,9,2,14);
commit;

Я хочу отсортировать эту таблицу в порядке убывания очков, голов_разницы и целей_для и присвоить рейтинг каждой команде на основе этого порядка. Поскольку в mySQL нет функций RANK, после поиска на этом сайте я придумал этот запрос.

SELECT CASE
          WHEN @prev_value = concat(points,'-',goal_difference,'-',goals_for)
          THEN
             @cur_rank
          WHEN @prev_value := concat(points,'-',goal_difference,'-',goals_for)
          THEN
             @cur_rank := @cur_rank + 1
       END
          AS rank, s.team_id, s.points, s.goal_difference, s.goals_for
  FROM standings s, (SELECT @cur_rank := 0) p, (SELECT @prev_rank := 0) q, (SELECT     @prev_value := NULL) r
 ORDER BY s.points DESC, s.goal_difference DESC, s.goals_for DESC;

Все идет нормально. Теперь у меня два вопроса.

  1. Если вы видите результат вышеприведенного запроса, между командами 8 и 10 есть ничья для 7-го места. Итак, я хочу присвоить ранг № 9 следующей команде № 3. Как мне это сделать, не добавляя больше столбцов в запрос.

  2. Я хочу создать VIEW, используя этот запрос. Но mySQL не позволяет мне создать его и выдает ошибку: «Вид SELECT содержит переменную или параметр». Пожалуйста, предложите, как создать VIEW для этого.

    CREATE VIEW view_standings
AS
   SELECT CASE
     WHEN @prev_value = concat(points,'-',goal_difference,'-',goals_for)
     THEN
        @cur_rank
     WHEN @prev_value := concat(points,'-',goal_difference,'-',goals_for)
     THEN
        @cur_rank := @cur_rank + 1
  END
     AS rank,s.team_id,s.points,s.goal_difference,s.goals_for
FROM standings s,(SELECT @cur_rank := 0) p,(SELECT @prev_rank := 0) q,(SELECT @prev_value := NULL) r
ORDER BY s.points DESC, s.goal_difference DESC, s.goals_for DESC;

mysql view rank
person Noel    schedule 29.04.2013    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Вы также можете выполнять ранжирование, используя коррелированные подзапросы. Если у вас есть даже небольшой объем данных, это может потребовать значительных вычислительных ресурсов.

 select s.*,
        (select 1+COUNT(*)
         from standings s2
         where s2.points > s.points or
               (s2.points = s.points and s2.goal_difference > s.goal_difference) or
               (s2.points = s.points and s2.goal_difference = s.goal_difference and s2.goals_for > sys.goals_for
        ) as ranking
 from standings s

Поскольку у него есть только подзапрос в предложении from, вы можете использовать его как представление.

Я думаю, вы можете улучшить производительность, имея индекс № standings(points, goal_difference, goals_for).

person Gordon Linoff    schedule 29.04.2013
comment
Спасибо, Гордон. Это именно то, что я хотел. - person Noel; 29.04.2013