Турнирную таблица из таблицы игр

[una1ded]

Турнирную таблица из таблицы игр [РЕШЕНО]

Доброго дня, коллеги. Прошу помощи, ибо сам уже зашел в тупик.

Есть таблица teams поля, упрощенно:
id (int)
name (varchar)

Есть таблица games с полями
id (int) — id игры
done (пусть тоже int, не критично) — признак того, что игру "сыграли": 0 или 1 (логичнее тут использовать BOOLEAN, но сейчас нет никакой разницы)
k1 (int) — id команды1
k2 (int) — id команды2
g1 (int) — сколько забила команда1
g2 (int) — сколько забила команда2

Задача: построить турнирную таблицу, речь идет о футболе, используя разумное число запросов.
В турнирной таблице отражаются следующие показатели:
И — число сыграных командой игр
В — сколько игр выиграно
П — сколько игр проиграно
Н — сколько игр сыграно в ничью
МЗ, МП — соотв. число забитых/пропущенных мячей
О — очки (за победу - 3 очка, за ничью -1 очко, за поражение — ноль)

Решаю связкой MySQL5 + PHP5.
Показатель «И» считаю 2мя запросами, суммируя потом результат:

SELECT k1, COUNT( * ) FROM `test` WHERE done=1 GROUP BY k1;
SELECT k2, COUNT( * ) FROM `test` WHERE done=1 GROUP BY k2;

Приходится делать так, ибо одна и та же команда может быть как k1, так и k2

Прошу помощи вот в каких вопросах
0) Рационален ли такой способ подсчета «И» или подсчитать более рационально? Может одним запросом?
1) Каким образом можно подсчитать показатели В,П,Н,О?
2) нормально ли организована БД? Т.е. рационально ли вообще из такой таблице высчитывать такие показатели? Есть мысль разбить таблицу games на две таблицы: (id, done) и (id,game,k,g), т.о. выборку по командам будет делать легче, но с кол-вом выигрышей-проигрышей все равно не ясно.

Заранее спасибо!

 

[Gas]

В — сколько игр выиграно

SUM(IF(g1>g2,1,0)) AS wins
остальное по аналогии

 

[crocodile2u]

сделай таблицу team_game:
team_id
result(won,lost,draw)
scored
conceded

(получится, что одна запись в твоей таблице отобразится на две записи в team_game: по одной для каждой команды). Причем, эту таблицу можно заполнять с помощью триггера, навешенного на ИНСЕРТ в имеющуюся у тебя таблицу.

 

[una1ded]

Gas
Не знал о существовании такой конструкции. Пробую.
Спасибо!

crocodile2u
В этом же направлении я думал (п. 2)

UPDATE: решено. названия полей изменены (уже в рабочую таблицу перенес), но если кому понадобится — разберется.
А вот и решение:

select TeamName,
sum(case when g.HTeam = t.Team then HGoals else CGoals end) as MZ,
sum(case when g.HTeam = t.Team then CGoals else HGoals end) as MP,
sum(case when g.HTeam = t.Team and HGoals > CGoals then 3
when g.CTeam = t.Team and CGoals > HGoals then 3
when CGoals = HGoals then 1
else 0 end) as Points,
SUM(case when g.HTeam = t.Team and HGoals > CGoals then 1
when g.CTeam = t.Team and CGoals > HGoals then 1
else 0 end) as W,
SUM(case when g.HTeam = t.Team and HGoals < CGoals then 1
when g.CTeam = t.Team and CGoals < HGoals then 1
else 0 end) as P,
SUM(case when CGoals = HGoals then 1
else 0 end) as N,
SUM(case when g.HTeam = t.Team or g.CTeam = t.Team then 1
else 0 end) as I
from Teams as t
join Games as g on (g.HTeam = t.Team or g.CTeam = t.Team)
group by TeamName
order by Points desc