mysql - 多个内部联接或多个查询？

Question

我需要显示一个表格，其中列出了游戏名称、开发商和发行商。每个游戏可以有多个开发商和发行商。从下图中，是否可以使用内部联接通过一个查询获取所有这些信息，还是我必须进行多个数据库事务？

就目前而言，我已经能够获得游戏的开发者(单数)，但我不确定如何在一个查询中获取多个开发者和多个发行商。到目前为止，这是我的查询:

SELECT games.id, games.title, companies.id, companies.name
INNER JOIN game_developer ON game_developer.games_id = games.id
INNER JOIN companies ON companies.id = game_developer.companies_id
GROUP BY games.title
ORDER BY games.title
LIMIT 50

最终产品应如下所示:

Answer 1

使用GROUP_CONCAT 获取所有名称:

SELECT games.id, games.title, 
        GROUP_CONCAT(DISTINCT dc.name) AS developers,
        GROUP_CONCAT(DISTINCT pc.name) AS publishers
INNER JOIN game_developer ON game_developer.games_id = games.id
INNER JOIN companies AS dc ON dc.id = game_developer.companies_id
INNER JOIN game_publisher AS gp ON gp.games_id = games.id
INNER JOIN companies AS dp ON dp.id = gp.companies_id
GROUP BY games.id
ORDER BY games.title
LIMIT 50

您需要使用 DISTINCT，因为同时加入发布商和开发人员会生成包含大量重复项的交叉产品。