左多次连接同一个表

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.

在我看来，这应该是在其他情况下解决的问题。将在这里感谢所有帮助。

3 个解决方案

#1

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 are alternatives 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)

EDIT JOIN free version, assuming presence of player_id field...

EDIT JOIN免费版，假设存在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

#2

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.

ofcourse 9999999应该是最大可能的用户ID -1。这将针对大分组查询交换先前答案的子查询。

Tested on MySQL 5.1 Ubuntu Lucid with your test data.

使用您的测试数据在MySQL 5.1 Ubuntu Lucid上进行测试。

#3

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