Реализация турнирной таблицы MySQL + PHP

187

08 июня 2018, 10:50

Есть запрос вывода таблицы чемпионата. Как преобразовать эту таблицу в нормальную форму. То есть, команде присваивается за победу 3 очка, за ничью 1, а за поражение 0. Ну и отсортировать ее по очкам. Как это можно организовать?

    select id_team, 
  sum(win) as win, 
  sum(loss) as loss, 
  sum(draw) as draw, 
  sum(goals_s) as goals_s, 
  sum(goals_m) as goals_m 
from (
select games.id_team_one as id_team, 
  IF(games.goals_one>games.goals_two,1,0) as win,
  IF(games.goals_one<games.goals_two,1,0) as loss,
  IF(games.goals_one=games.goals_two,1,0) as draw,
  games.goals_one as goals_s,
  games.goals_two as goals_m
from games
where games.`datetime` < NOW()
union all
select games.id_team_two, 
  IF(games.goals_one<games.goals_two,1,0) as win,
  IF(games.goals_one>games.goals_two,1,0) as loss,
  IF(games.goals_one=games.goals_two,1,0) as draw,
  games.goals_two as goals_s,
  games.goals_one as goals_m
from games
where games.`datetime` < NOW()
) t group by id_team

Answer 1

select *,
    win * 3 as win_ace,
    draw * 1 as draw_ace
from(
    SELECT id_team,
           sum(win) AS win,
           sum(loss) AS loss,
           sum(draw) AS draw,
           sum(goals_s) AS goals_s,
           sum(goals_m) AS goals_m
    FROM
        (SELECT games.id_team_one AS id_team,
                IF(games.goals_one>games.goals_two,1,0) AS win,
                IF(games.goals_one<games.goals_two,1,0) AS loss,
                IF(games.goals_one=games.goals_two,1,0) AS draw,
                games.goals_one AS goals_s,
                games.goals_two AS goals_m
         FROM games
         WHERE games.`datetime` < NOW()
         UNION ALL SELECT games.id_team_two,
                          IF(games.goals_one<games.goals_two,1,0) AS win,
                          IF(games.goals_one>games.goals_two,1,0) AS loss,
                          IF(games.goals_one=games.goals_two,1,0) AS draw,
                          games.goals_two AS goals_s,
                          games.goals_one AS goals_m
         FROM games
         WHERE games.`datetime` < NOW() ) t
    GROUP BY id_team
) as foo
order by ((win * 3) + (draw * 1)) desc;