Добавление дополнительного столбца в таблицу
Мне нужна ваша помощь, потому что я понятия не имею, как я могу это сделать:
У меня есть три таблицы MySQL:
pcs_clubs идентификатор, короткое имя
pcs_candidates id, id_compet, id_club, id_season
pcs_matchs id, id_candidate_dom, id_candidate_ext, id_day, date_day, buts_dom, buts_ext, id_season, id_compet, проверка
Образец данных:
pcs_clubs
id shortname
1 Barcelona
2 Real Madrid
3 Sevilla
4 Deportivo
5 Espanyol
6 Real Zaragoza
pcs_candidates
id id_compet id_club id_season
1 1 1 1
2 1 2 1
3 1 3 1
4 1 4 1
5 1 5 1
6 1 6 1
pcs_matchs
+----+------------------+------------------+--------+------------+----------+----------+-----------+-----------+------------+
| id | id_candidate_dom | id_candidate_ext | id_day | date_match | buts_dom | buts_ext | id_season | id_compet | validation |
+----+------------------+------------------+--------+------------+----------+----------+-----------+-----------+------------+
| 1 | 1 | 6 | 1 | 2015-11-12 | 0 | 3 | 1 | 1 | 1 |
+----+------------------+------------------+--------+------------+----------+----------+-----------+-----------+------------+
| 2 | 2 | 5 | 1 | 2015-11-12 | 2 | 2 | 1 | 1 | 1 |
+----+------------------+------------------+--------+------------+----------+----------+-----------+-----------+------------+
| 3 | 3 | 4 | 1 | 2015-11-12 | 2 | 1 | 1 | 1 | 1 |
+----+------------------+------------------+--------+------------+----------+----------+-----------+-----------+------------+
| 4 | 6 | 2 | 2 | 2015-11-17 | 3 | 1 | 1 | 1 | 1 |
+----+------------------+------------------+--------+------------+----------+----------+-----------+-----------+------------+
| 5 | 4 | 1 | 2 | 2015-11-17 | 1 | 1 | 1 | 1 | 1 |
+----+------------------+------------------+--------+------------+----------+----------+-----------+-----------+------------+
| 6 | 5 | 3 | 2 | 2015-11-17 | 1 | 2 | 1 | 1 | 1 |
+----+------------------+------------------+--------+------------+----------+----------+-----------+-----------+------------+
| 7 | 3 | 6 | 3 | 2015-11-24 | 2 | 0 | 1 | 1 | 1 |
+----+------------------+------------------+--------+------------+----------+----------+-----------+-----------+------------+
| 8 | 2 | 4 | 3 | 2015-11-24 | 3 | 1 | 1 | 1 | 1 |
+----+------------------+------------------+--------+------------+----------+----------+-----------+-----------+------------+
| 9 | 1 | 5 | 3 | 2015-11-24 | 4 | 1 | 1 | 1 | 1 |
+----+------------------+------------------+--------+------------+----------+----------+-----------+-----------+------------+
Следующий запрос MySQL успешно сгенерировал рейтинговую таблицу желаемого соревнования и сезона:
SELECT
shortname AS Team, Sum(P) AS P,Sum(W) AS W,Sum(D) AS D,Sum(L) AS L,
SUM(F) as F,SUM(A) AS A,SUM(GD) AS GD,SUM(Pts) AS Pts
FROM(
SELECT
id_candidate_dom Team,
1 P,
IF(buts_dom > buts_ext,1,0) W,
IF(buts_dom = buts_ext,1,0) D,
IF(buts_dom < buts_ext,1,0) L,
buts_dom F,
buts_ext A,
buts_dom-buts_ext GD,
CASE WHEN buts_dom > buts_ext THEN 3 WHEN buts_dom = buts_ext THEN 1 ELSE 0 END PTS
FROM pcs_matchs
UNION ALL
SELECT
id_candidate_ext,
1,
IF(buts_dom < buts_ext,1,0),
IF(buts_dom = buts_ext,1,0),
IF(buts_dom > buts_ext,1,0),
buts_ext,
buts_dom,
buts_ext-buts_dom GD,
CASE WHEN buts_dom < buts_ext THEN 3 WHEN buts_dom = buts_ext THEN 1 ELSE 0 END
FROM pcs_matchs WHERE validation=1 AND id_compet=1 AND id_season=1
) as tot
JOIN pcs_candidates t ON tot.Team=t.id_club
JOIN pcs_clubs c ON c.id=t.id_club
GROUP BY Team
ORDER BY SUM(Pts) DESC, SUM(GD) DESC;
Текущий результат:
+------+---------------+---+---+---+---+---+----+-----+------------+
| | Team | P | W | D | L | F | A | GD | Pts |
+------+---------------+---+---+---+---+---+----+-----+------------+
| 1 | Sevilla | 3 | 3 | 0 | 0 | 6 | 2 | 4 | 9pts |
+------+---------------+---+---+---+---+---+----+-----+------------+
| 2 | Real Zaragoza | 3 | 2 | 0 | 1 | 6 | 3 | 3 | 6pts |
+------+---------------+---+---+---+---+---+----+-----+------------+
| 3 | Real Madrid | 3 | 1 | 1 | 1 | 6 | 6 | 0 | 4pts |
+------+---------------+---+---+---+---+---+----+-----+------------+
| 4 | Barcelona | 3 | 1 | 1 | 1 | 5 | 5 | 0 | 4pts |
+------+---------------+---+---+---+---+---+----+-----+------------+
| 5 | Deportivo | 3 | 0 | 1 | 2 | 3 | 6 | -3 | 1pts |
+------+---------------+---+---+---+---+---+----+-----+------------+
| 6 | Espanyol | 3 | 0 | 1 | 2 | 4 | 8 | -4 | 1pts |
+------+---------------+---+---+---+---+---+----+-----+------------+
Я хотел бы добавить дополнительный столбец (текущая форма) в таблицу лиги на основе последних 5 матчей каждой команды до текущей даты. Мне нужно просто, с добавлением одного символа за матчи, если команда выиграла то W, если ничья то D, если проиграла то L. (если команда еще не сыграла 5 матчей, то не добавлять символ в столбец) как-то так это:
Желаемый результат:
+---+---------------+---+---+---+---+---+---+----+------+-------------------------------------+
| | | | | | | | | | | Form (from newest result to oldest based on date of match) |
+---+---------------+---+---+---+---+---+---+----+------+-------------------------------------+
| 1 | Sevilla | 3 | 3 | 0 | 0 | 6 | 2 | 4 | 9pts | WWW |
+---+---------------+---+---+---+---+---+---+----+------+-------------------------------------+
| 2 | Real Zaragoza | 3 | 2 | 0 | 1 | 6 | 3 | 3 | 6pts | LWW |
+---+---------------+---+---+---+---+---+---+----+------+-------------------------------------+
| 3 | Real Madrid | 3 | 1 | 1 | 1 | 6 | 6 | 0 | 4pts | WLD |
+---+---------------+---+---+---+---+---+---+----+------+-------------------------------------+
| 4 | Barcelona | 3 | 1 | 1 | 1 | 5 | 5 | 0 | 4pts | WDL |
+---+---------------+---+---+---+---+---+---+----+------+-------------------------------------+
| 5 | Deportivo | 3 | 0 | 1 | 2 | 3 | 6 | -3 | 1pts | LDL |
+---+---------------+---+---+---+---+---+---+----+------+-------------------------------------+
| 6 | Espanyol | 3 | 0 | 1 | 2 | 4 | 8 | -4 | 1pts | LLD |
+---+---------------+---+---+---+---+---+---+----+------+-------------------------------------+
https://www.flashscore.com/standings/pvZBCt9T/vHE9SNDE/#live
Заранее спасибо!
ОБНОВЛЕННЫЙ ЗАПРОС (похоже, он работает)
SELECT
shortname AS Team, Sum(P) AS P,Sum(W) AS W,Sum(D) AS D,Sum(L) AS L,
SUM(F) as F,SUM(A) AS A,SUM(GD) AS GD,SUM(Pts) AS Pts, date_day AS dateday, group_concat(match_result ORDER BY date_day DESC)
FROM(
SELECT
id_candidate_dom Team,
1 P,
IF(buts_dom > buts_ext,1,0) W,
IF(buts_dom = buts_ext,1,0) D,
IF(buts_dom < buts_ext,1,0) L,
buts_dom F,
buts_ext A,
buts_dom-buts_ext GD,
CASE WHEN buts_dom > buts_ext THEN 3 WHEN buts_dom = buts_ext THEN 1 ELSE 0 END PTS,
CASE
WHEN buts_dom > buts_ext THEN 'W'
WHEN buts_dom = buts_ext THEN 'D'
WHEN buts_dom < buts_ext THEN 'L'
END match_result,
date_day
FROM pcs_matchs
UNION ALL
SELECT
id_candidate_ext,
1,
IF(buts_dom < buts_ext,1,0),
IF(buts_dom = buts_ext,1,0),
IF(buts_dom > buts_ext,1,0),
buts_ext,
buts_dom,
buts_ext-buts_dom GD,
CASE WHEN buts_dom < buts_ext THEN 3 WHEN buts_dom = buts_ext THEN 1 ELSE 0 END,
CASE
WHEN buts_dom < buts_ext THEN 'W'
WHEN buts_dom = buts_ext THEN 'D'
WHEN buts_dom > buts_ext THEN 'L'
END match_result,
date_day
FROM pcs_matchs WHERE validation=1 AND id_compet=1 AND id_season=1
) as tot
JOIN pcs_candidates t ON tot.Team=t.id_club
JOIN pcs_clubs c ON c.id=t.id_club
GROUP BY Team
ORDER BY SUM(Pts) DESC, SUM(GD) DESC
Привет, спасибо за ответ. Я не хочу добавлять другую таблицу для результатов. (было бы огромной работой изменить весь сайт). Просто я хотел бы добавить аббревиатуру результата (W или D или L) по раундам в последнюю колонку таблицы лиги (форма команды). Я думаю, что какой-нибудь эксперт mysql мог бы легко обновить мой код для получения желаемого результата (например, если команда выиграет, поставьте символ W, а если команда проиграла в следующем раунде, поставьте символ L, а если результат матча - ничья, то символ D ). Я не хочу показывать форму последних месяцев. Я надеюсь, что кто-то поможет мне обновить мой код...
Я не думаю, что вы можете сделать это с той структурой, которая у вас есть, потому что как вы можете знать, какие были последние 3 результата, не привязывая результат к дате матча - вы можете получить только итоги. Если вы не хотите сохранять результаты, вы можете добавить «форму» в качестве столбца в свою таблицу, и каждый раз, когда вы обновляете, вы можете использовать CONCAT, как я предложил, когда вы обновляете таблицу - вы хотели бы SUBSTR последние два символы SET form = CONCAT(SUBSTR(form, 2),'new_value')) - я не понимаю, как вы можете сделать это иначе без связи между результатами и датой
Ответы 1
После добавления столбцов date_match и match_result к запросам в UNION добавьте следующий столбец в первое предложение SELECT:
group_concat(match_result ORDER BY date_match DESC)
Для результата матча:
CASE
WHEN buts_dom > buts_ext THEN 'W'
WHEN buts_dom = buts_ext THEN 'D'
WHEN buts_dom < buts_ext THEN 'L'
END match_result
столбца match_result нет?
да, в таблице pcs_matchs есть только столбцы buts_dom (гол дома) и buts_ext (гол на выезде), но есть столбец date_match
Здравствуйте, Сэл, спасибо за ваш ответ, я добавил обновленный код в конец своего вопроса: результат: # 1222 - Используемые операторы SELECT имеют другое количество столбцов. И имя столбца даты матча - date_day, ранее я написал неправильно.
@Terminus: вам не хватает date_day во втором запросе UNION.
Большое спасибо, Сэл, похоже, это работает, сейчас я тестирую и получаю больше результатов. Я надеюсь, что это сработает, еще раз спасибо!
вы можете использовать
CONCATв обновлении (например,SET col = CONCAT(col,'new_value')), но, вероятно, было бы полезнее и лучше нормализации иметь другую таблицу для результатов и выполнять запрос соединения/подзапроса, когда вам нужно также отобразить форму - что, если (в будущее) вы хотите показать форму за последние 5 месяцев или лет, в зависимости от страницы/запроса