MySQL 多次左连接同一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8564486/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Left-joining the same table multiple times
提问by ObiObi
Suppose I have a game that can be played by 2, 3 or 4 players. I track such a game in my database (MySQL 5.1) in three tables, given below. I am hoping that the fields are self-explanatory:
假设我有一个可以由 2、3 或 4 名玩家玩的游戏。我在我的数据库 (MySQL 5.1) 中的三个表中跟踪这样的游戏，如下所示。我希望这些字段是不言自明的：
create table users (id int, login char(8));
create table games (id int, stime datetime, etime datetime);
create table users_games (uid int, gid int, score int);
[The two times tracked in the games table are the start and end time]
【游戏表中记录的两次为开始时间和结束时间】
Here is some dummy data to populate the tables:
这是一些用于填充表的虚拟数据：
insert into games values
(1, '2011-12-01 10:00:00', '2011-12-01 13:00:00'),
(2, '2011-12-02 11:00:00', '2011-12-01 14:00:00'),
(3, '2011-12-03 12:00:00', '2011-12-01 15:00:00'),
(4, '2011-12-04 13:00:00', '2011-12-01 16:00:00');
insert into users_games values
(101, 1, 10),
(102, 1, 11),
(101, 2, 12),
(103, 2, 13),
(104, 2, 14),
(102, 3, 15),
(103, 3, 16),
(104, 3, 17),
(105, 3, 18),
(102, 4, 19),
(104, 4, 20),
(105, 4, 21);
Now, I need to produce a report in the following format:
现在，我需要生成以下格式的报告：
gid p1 p2 p3 p4 started ended
1 101 102 [g1] [g1]
2 101 103 104 [g2] [g2]
3 102 103 104 105 [g3] [g3]
4 102 104 105 [g4] [g4]
That is, a report that shows all the players who played a game in the same row. I also need their scores and some other information from the users table, but that is phase 2. :-)
即，显示在同一行中玩过游戏的所有玩家的报告。我还需要他们的分数和用户表中的其他一些信息，但这是第 2 阶段。:-)
I started with this:
我从这个开始：
select g.id, g.stime, g.etime, ug1.uid, ug2.uid, ug3.uid, ug4.uid
from games g, users_games ug1, users_games ug2, users_games ug3, users_games ug4
where
g.id = ug1.gid and
ug1.gid = ug2.gid and
ug1.uid < ug2.uid and
ug2.gid = ug3.gid and
ug2.uid < ug3.uid and
ug3.gid = ug4.gid and
ug3.uid < ug4.uid
This gives me all games where all four seats were occupied (ie, only game ID 3 in the above dummy data). But that is only a subset of the data I need.
这给了我所有四个席位都被占用的游戏（即，上述虚拟数据中只有游戏 ID 3）。但这只是我需要的数据的一个子集。
This is my second attempt:
这是我的第二次尝试：
select g.id, g.stime, g.etime, ug1.uid, ug2.uid,
ifnull(ug3.uid, ''), ifnull(ug4.uid, '')
from ( games g, users_games ug1, users_games ug2 )
left join users_games ug3 on ug2.gid = ug3.gid and ug2.uid < ug3.uid
left join users_games ug4 on ug3.gid = ug4.gid and ug3.uid < ug4.uid
where
g.id = ug1.gid and
ug1.gid = ug2.gid and
ug1.uid < ug2.uid
This gives me 14 rows with the above dummy data. I tried to eliminate one source of error by anchoring ug1 to the entry for the lowest-UID player:
这给了我 14 行上面的虚拟数据。我试图通过将 ug1 锚定到最低 UID 播放器的条目来消除一个错误源：
select g.id, g.stime, g.etime, ug1.uid, ug2.uid,
ifnull(ug3.uid, ''), ifnull(ug4.uid, '')
from
( games g, users_games ug1, users_games ug2,
(select gid as g, min(uid) as u from users_games group by g) as xx
)
left join users_games ug3 on ug2.gid = ug3.gid and ug2.uid < ug3.uid
left join users_games ug4 on ug3.gid = ug4.gid and ug3.uid < ug4.uid
where
g.id = xx.g and
ug1.uid = xx.u and
g.id = ug1.gid and
ug1.gid = ug2.gid and
ug1.uid < ug2.uid
Now I am down to 9 rows, but I still have a lot of spurious data. I can see the problem - that for example in game 3, with ug1 anchored to user 102, there are still three players to whom ug2 can be anchored. And so on. But I cannot figure out a way to solve this conundrum - how can I ultimately achieve a query that will output 4 rows with the players in the correct order and number?
现在我减少到 9 行，但我仍然有很多虚假数据。我可以看到问题 - 例如在游戏 3 中，将 ug1 锚定到用户 102，仍然可以将 ug2 锚定到三个玩家。等等。但我想不出解决这个难题的方法 - 我怎样才能最终实现一个查询，以正确的顺序和数字输出 4 行的玩家？
This appears to me should be a solved problem in other contexts. Will appreciate all help here.
在我看来，这在其他情况下应该是一个已解决的问题。将感谢这里的所有帮助。
回答by MatBailie
One problem you have is that you have no fields that describe a user as Player 1, 2, 3 or 4. Yet, you need to ensure that only one player is joined per LEFT JOIN.
您遇到的一个问题是您没有将用户描述为玩家 1、2、3 或 4 的字段。但是，您需要确保每个 LEFT JOIN 只有一名玩家加入。
If you add a "player_id" field to users_games, it becomes trivial...
如果你给 users_games 添加一个“player_id”字段，它就变得微不足道了……
SELECT
*
FROM
games
LEFT JOIN
users_games AS p1
ON p1.gid = games.id
AND p1.player_id = 1
LEFT JOIN
users_games AS p2
ON p2.gid = games.id
AND p2.player_id = 2
LEFT JOIN
users_games AS p3
ON p3.gid = games.id
AND p3.player_id = 3
LEFT JOIN
users_games AS p4
ON p4.gid = games.id
AND p4.player_id = 4
There arealternatives that avoid all the LEFT JOINs, but this examples serves well as it is the basis for the next step...)
有一些替代方法可以避免所有的 LEFT JOIN，但是这个例子很好用，因为它是下一步的基础......）
If you can't add this field, it becomes more complex. (SQL Server, Oracle, etc, can proxy this player_id field using ROW_NUMBER(), MySQL can't.)
如果你不能添加这个字段，它会变得更加复杂。（SQL Server、Oracle 等可以使用 ROW_NUMBER() 代理这个 player_id 字段，MySQL 不能。）
Instead, you need correlated sub-queries to identify the 'next player'.
相反，您需要相关的子查询来识别“下一个玩家”。
SELECT
*
FROM
games
LEFT JOIN
users_games AS p1
ON p1.gid = games.id
AND p1.uid = (SELECT MIN(uid) FROM users_games WHERE gid = games.id)
LEFT JOIN
users_games AS p2
ON p2.gid = games.id
AND p2.uid = (SELECT MIN(uid) FROM users_games WHERE gid = games.id AND uid > p1.uid)
LEFT JOIN
users_games AS p3
ON p3.gid = games.id
AND p3.uid = (SELECT MIN(uid) FROM users_games WHERE gid = games.id AND uid > p2.uid)
LEFT JOIN
users_games AS p4
ON p4.gid = games.id
AND p4.uid = (SELECT MIN(uid) FROM users_games WHERE gid = games.id AND uid > p3.uid)
EDITJOIN free version, assuming presence of player_id field...
EDITJOIN 免费版本，假设存在 player_id 字段...
SELECT
games.id,
MAX(CASE WHEN users_games.player_id = 1 THEN users_games.uid END) AS p1_id,
MAX(CASE WHEN users_games.player_id = 2 THEN users_games.uid END) AS p2_id,
MAX(CASE WHEN users_games.player_id = 3 THEN users_games.uid END) AS p3_id,
MAX(CASE WHEN users_games.player_id = 4 THEN users_games.uid END) AS p4_id
FROM
games
LEFT JOIN
users_games
ON users_games.gid = games.id
GROUP BY
games.id
回答by Eugen Rieck
SELECT games.*,
IF(min(ifnull(ug1.uid,9999999))=9999999,null,ug1.uid) AS user1,
IF(min(ifnull(ug2.uid,9999999))=9999999,null,ug2.uid) AS user2,
IF(min(ifnull(ug3.uid,9999999))=9999999,null,ug3.uid) AS user3,
IF(min(ifnull(ug4.uid,9999999))=9999999,null,ug4.uid) AS user4
FROM games
LEFT JOIN users_games AS ug1 ON ug1.gid=games.id
LEFT JOIN users_games AS ug2 ON ug2.gid=games.id AND ug2.uid>ug1.uid
LEFT JOIN users_games AS ug3 ON ug3.gid=games.id AND ug3.uid>ug2.uid
LEFT JOIN users_games AS ug4 ON ug4.gid=games.id AND ug4.uid>ug3.uid
GROUP BY games.id
ofcourse 9999999 should be the maximum possible user id -1. This trades the subqueries of the previous answer against a big grouping query.
当然 9999999 应该是最大可能的用户 ID -1。这将先前答案的子查询与大分组查询进行交换。
Tested on MySQL 5.1 Ubuntu Lucid with your test data.
使用您的测试数据在 MySQL 5.1 Ubuntu Lucid 上进行了测试。
回答by symcbean
Wouldn't it be simpler to just.....
只是……不是更简单吗？
SELECT g.id, GROUP_CONCAT(u.login ORDER BY u.login), g.stime, g.etime
FROM games g,
users u,
users_games ug
WHERE ug.gid=g.id
AND ug.uid=u.id
GROUP BY g.id, g.stime, g.etime
And if you want scores, just add a function, then...
如果你想要分数，只需添加一个函数，然后......
SELECT g.id, GROUP_CONCAT(
CONCAT(u.login, '=', get_score(u.login, g.id)) ORDER BY 1
), g.stime, g.etime
FROM games g,
users u,
users_games ug
WHERE ug.gid=g.id
AND ug.uid=u.id
GROUP BY g.id, g.stime, g.etime
