左-多次连接同一个表

Question

假设我有一个可以由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);

游戏表中跟踪的两个时间是开始时间和结束时间。

下面是一些用于填充表的虚拟数据：

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);

现在，我需要生成以下格式的报告：

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]

也就是说，该报告显示了在同一行中玩游戏的所有玩家。我还需要他们的分数和用户表中的一些其他信息，但这是阶段2。

我是这样开始的：

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

这给了我所有四个座位都被占用的所有游戏(即，上面的虚拟数据中只有游戏ID 3)。但这只是我需要的数据的一个子集。

这是我的第二次尝试：

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

这给了我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

现在我只剩下9行了，但是我仍然有很多虚假数据。我可以看到问题所在--例如，在游戏3中，当ug1锚定到用户102时，仍然有三个玩家可以锚定ug2。诸若此类。但是我想不出一个方法来解决这个难题--我如何最终实现一个查询，以正确的顺序和数字输出4行玩家？

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

Answer 1

您遇到的一个问题是，您没有将用户描述为玩家1、2、3或4的字段。但是，您需要确保每个左连接只有一个玩家加入。

如果您向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

有一些替代方法可以避免所有的左连接，但这个示例很好地服务于下一步的基础……)

如果你不能添加这个字段，它会变得更加复杂。(SQL Server、Oracle等可以使用ROW_NUMBER()代理此player_id字段，但MySQL不能。)

相反，你需要相关子查询来识别“下一个玩家”。

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)

编辑 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

Answer 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

当然，9999999应该是最大可能的用户id -1。这会将前一个答案的子查询与一个大的分组查询进行交换。

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

Answer 3

是不是更简单……

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

如果你想要分数，只需添加一个函数，然后...

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