Вопрос по mysql, sql – Как объединить несколько таблиц, включая таблицу поиска, и возвращать данные в строках

4

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

Team A (score 45) vs. Team B (score 55), game duration: 5 mins Team C (score 60) vs. Team D (score 65), game duration: 4.3 mins

Таким образом, для игры есть две команды, которые играют друг с другом, и каждая из них получает счет в конце игры. По сути, в таблице games_teams для каждой игры есть две строки.

Вот моя схема:

schema pic

Вот данные моей таблицы:

table data pic

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

desired output pic

Мне удалось добиться этого с помощью какого-то ужасающего SQL и множества подзапросов, например:

SELECT games.game_id, game_name, game_duration, 
(SELECT team_id FROM games_teams WHERE games.game_id = games_teams.game_id LIMIT 0, 1) AS team_id_a,
(SELECT team_id FROM games_teams WHERE games.game_id = games_teams.game_id LIMIT 1, 1) AS team_id_b,
(SELECT teams.team_name FROM games_teams INNER JOIN teams ON games_teams.team_id = teams.team_id WHERE games.game_id = game_id LIMIT 0, 1) AS team_name_a,
(SELECT teams.team_name FROM games_teams INNER JOIN teams ON games_teams.team_id = teams.team_id WHERE games.game_id = game_id LIMIT 1, 1) AS team_name_b,
(SELECT team_score FROM games_teams WHERE games.game_id = games_teams.game_id LIMIT 0, 1) AS team_score_a,
(SELECT team_score FROM games_teams WHERE games.game_id = games_teams.game_id LIMIT 1, 1) AS team_score_b
FROM games

Проблема с этим способом состоит в том, что он будет медленным и не масштабируется. Мне также нужно извлечь статистику других игр из таблицы games_teams, чтобы "было еще больше подзапросов".

Другой метод, который я попробовал, был:

not desired output pic

Я достиг этого с помощью следующего SQL:

SELECT games.game_id, game_name, game_duration, teams.team_id, team_name, team_score
FROM games
INNER JOIN games_teams ON games.game_id = games_teams.game_id
INNER JOIN teams ON games_teams.team_id = teams.team_id

Теперь этот путь будет труднее найти в коде, поскольку соответствующие данные для каждой игры находятся в двух разных записях. Мне нужно построить первую часть строки, затем перейти к следующей итерации цикла и распечатать следующую часть. Затем начните все сначала для следующей игры, я пытаюсь отобразить всю информацию в одну строку, например:

Команда A (оценка 45) против команды B (оценка 55), продолжительность игры: 5 минут

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

Здесь ссылка на вставку с кодом базы данных, если вам нужно воссоздать его.

Любая помощь высоко ценится, спасибо!

Ваш Ответ

2   ответа
4

Вам нужно будет дважды присоединиться к games_teams и командам, например:

SELECT ga.game_id
        , ga.game_name
        , ga.game_duration
        , t1.team_name, gt1.team_score
        , t2.team_name, gt2.team_score
FROM games ga
JOIN games_teams  gt1 ON gt1.game_id = ga.game_id
JOIN games_teams  gt2 ON gt2.game_id = ga.game_id
JOIN teams t1 ON t1.team_id = gt1.team_id
JOIN teams t2 ON t2.team_id = gt2.team_id
WHERE gt1.team_id < gt2.team_id
        ;

Самый простой способ выжать вложенное объединение {games_teams * команды} и дважды обратиться к нему - это поместить его в CTE: (к сожалению, mysql не поддерживает CTE)

WITH gtx AS (
        SELECT gt.game_id
        , gt.team_score
        , te.team_id
        , te.team_name
        FROM games_teams gt
        JOIN teams te ON te.team_id = gt.team_id
        )
SELECT ga.game_id 
        , ga.game_name
        , ga.game_duration
        , g1.team_name, g1.team_score
        , g2.team_name, g2.team_score
FROM games ga
JOIN gtx g1 ON g1.game_id = ga.game_id
JOIN gtx g2 ON g2.game_id = ga.game_id
WHERE g1.team_id < g2.team_id
  ;

Результат:

 game_id | game_name | game_duration | team_name | team_score | team_name | team_score 
---------+-----------+---------------+-----------+------------+-----------+------------
       1 | Game A    |           300 | Team A    |         45 | Team B    |         55
       2 | Game B    |           258 | Team C    |         60 | Team D    |         65
(2 rows)
Кстати: «нарушение симметрии» условие (g1.team_id & lt; g2.team_id) является произвольным. Вместо этого вы можете использовать суррогатный ключ (game_team_id).
Нет, я думаю, что вы должны сделать это самостоятельно (так как этоobviously домашнее задание)
Да, это очень хорошо, работает отлично, спасибо !!! zuallauz
Выглядит хорошо. Может стать важным, когда команды team1 и team2 фактически заказаны, например, дома / вне для футбола, или играть в чёрный / играть в белый для шахмат.
Можете ли вы привести пример, используя суррогатный ключ game_team_id? zuallauz
1

Это упрощается тем, что в каждой игре всегда ровно две команды, поэтому вы можете использовать UNION для получения команд для каждой игры, например:

SELECT q2.game_id, games.game_name, games.game_duration,
       q2.team_id_a, q2.team_id_b,
       teams_a.team_name AS team_name_a,
       teams_b.team_name AS team_name_b,
       games_teams_a.team_score AS team_score_a,
       games_teams_b.team_score AS team_score_b
FROM  (SELECT q.game_id,
              MAX(q.team_id_a) AS team_id_a,
              MAX(q.team_id_b) AS team_id_b
          FROM (
               SELECT games_teams.game_id,
                      MIN(games_teams.team_id) AS team_id_a,
                      0 AS team_id_b
               FROM   games_teams
               GROUP BY games_teams.game_id
            UNION
               SELECT games_teams.game_id,
                      0 AS team_id_a,
                      MAX(games_teams.team_id) AS team_id_b
               FROM   games_teams
               GROUP BY games_teams.game_id
         ) q
        GROUP BY q.game_id) q2
   INNER JOIN games ON q2.game_id = games.game_id
   INNER JOIN teams teams_a ON q2.team_id_a = teams_a.team_id
   INNER JOIN teams teams_b ON q2.team_id_b = teams_b.team_id
   INNER JOIN games_teams games_teams_a ON
              q2.game_id = games_teams_a.game_id AND q2.team_id_a = games_teams_a.team_id
   INNER JOIN games_teams games_teams_b ON
              q2.game_id = games_teams_b.game_id AND q2.team_id_b = games_teams_b.team_id;

Похожие вопросы