如何联接多个表(包括查找表)并按行返回数据 [英] How to join multiple tables including lookup table and return data in rows

问题描述

我试图显示一些简单的计算机游戏结果，并使其易于在代码中逐行迭代结果.我想要这样，以便每个游戏的所有相关数据都在每个记录中，这样我就可以在一行上全部输出，例如:

I'm trying to display some simple computer game results and make it easy to iterate through the results line by line in my code. I want it so that all the relevant data for each game is in each record so I can output it all on the one line e.g.:

• A队(得分 45 )与 B队(得分 55 )，游戏持续时间: 5 分钟
• Team C (得分 60 )与 Team D (得分 65 )，游戏持续时间: 4.3 分钟

• 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

因此，对于一场比赛，有两支球队互相比赛，他们各自在比赛结束时得到一个分数.基本上，每个游戏的game_teams表中都有两行.

So for a game there is two teams that play each other and they each get a score at the end of the game. Essentially there ends up being two rows in the games_teams table for every game.

这是我的模式:

这是我的表格数据:

这是我要实现的输出，因此我可以轻松地遍历结果并将其输出到页面上:

Here's the output I'm trying to achieve so I can easily iterate through the results and output them on the page:

我设法通过一些可怕的SQL和许多子查询来实现这一点:

I managed to achieve that with some horrific SQL and lots of subqueries like so:

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表中提取其他游戏统计信息，以便获得更多子查询.

Problem with that method is it'll be slow and it doesn't scale. I also need to pull out other game stats from the games_teams table so that'll be even more subqueries.

我尝试过的另一种方法是:

The other method I tried was:

我通过以下SQL实现了这一目标:

I achieved that with the following 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

现在，由于每个游戏的相关数据都在两个不同的记录中，因此在代码中更难于通过这种方式进行讲解.我必须构建该行的第一部分，然后进入下一个循环迭代并打印下一个部分.然后为下一场游戏重新开始，我试图在一行上显示所有信息，例如:

Now this way will be harder to foreach through in the code as the relevant data for each game is in two different records. I'd have to build the first part of the row, then go into the next loop iteration and print the next part. Then start it all over again for the next game, I'm trying to display all the information on one line like:

A队(得分45)与B队(得分55)，比赛时间:5分钟

Team A (score 45) vs Team B (score 55), game duration: 5mins

所以这就是为什么我认为如果全部记录在一个记录上会更容易.有没有一种方法可以很好地完成此任务，因此如果我在games_teams表中需要更多列，它也可以扩展?

So that's why I think it would be easier if it was all on the one record. Is there a way to accomplish this nicely and so it scales as well if I need more columns in the games_teams table?

如果您需要重新创建，这里有一个带数据库代码的pastebin链接.

非常感谢任何帮助，谢谢！

Any help much appreciated, thanks!

推荐答案

您需要两次加入games_teams和团队，例如:

You'll need to join the games_teams and teams twice, like:

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 * team}子联接并对其进行两次引用的一种干净方法是将其放入CTE :(不幸的是mysql不支持CTE)

A clean way to do squeeze out the {games_teams * teams} sub-join and refer to it twice is by putting it into a CTE: (unfortunately mysql does not support CTEs)

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)

这篇关于如何联接多个表(包括查找表)并按行返回数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！