mysql - 同一张表上的多个连接

Question

到目前为止，我有这个。

SELECT p1.player_id, p1.real_name, p2.player_id, p2.real_name,
    ->      m.scoreA, m.scoreB
    -> FROM matches m
    -> JOIN players p1 ON p1.player_id = m.playerA
    -> JOIN players p2 ON p2.player_id = m.playerB;
+-----------+------------------------------------+-----------+------------------------------------+--------+--------+
| player_id | real_name                          | player_id | real_name                          | scoreA | scoreB |
+-----------+------------------------------------+-----------+------------------------------------+--------+--------+
|        19 | Seong Hun Choi                     |         1 | Dong Nyoung Lee                    |      0 |      2 |
|        38 | Yun Sik Choi                       |         1 | Dong Nyoung Lee                    |      0 |      2 |
|       160 | Lei Wang                           |         1 | Dong Nyoung Lee                    |      0 |      2 |
|         3 | Seung Hyun Lee                     |         1 | Dong Nyoung Lee                    |      0 |      2 |
|        45 | Min Chul Kim                       |         1 | Dong Nyoung Lee                    |      0 |      2 |
|        23 | Sasha Hostyn                       |         1 | Dong Nyoung Lee                    |      2 |      4 |
|        52 | Juan Moreno Duran                  |         1 | Dong Nyoung Lee                    |      2 |      3 |
|        18 | Kang Dong Hyun                     |         1 | Dong Nyoung Lee                    |      2 |      1 |
|        22 | Jae Duck Lim                       |         1 | Dong Nyoung Lee                    |      1 |      2 |
|       108 | Jérémy Vansnick                  |         1 | Dong Nyoung Lee                    |      1 |      2 |

我想加入我的members 表中的更多列，而不改变我的匹配表 的大小。我尝试的一切似乎都会改变我的匹配表的大小。它从 46k 增加到超过 100 万。到目前为止，这是我尝试过的。

SELECT p1.player_id, p1.real_name, p2.player_id, p2.real_name, 
     m.scoreA, m.scoreB, mem1.player, mem2.player
FROM matches m
JOIN players p1 ON p1.player_id = m.playerA
JOIN members mem1 ON mem1.player = p1.player_id
JOIN players p2 ON p2.player_id = m.playerB
JOIN members mem2 ON mem2.player = p2.player_id;

我想也许我设置的相等部分有误，所以我尝试了这个。

SELECT p1.player_id, p1.real_name, p2.player_id, p2.real_name, 
     m.scoreA, m.scoreB, mem1.player, mem2.player
FROM matches m
JOIN players p1 ON p1.player_id = m.playerA
JOIN members mem1 ON mem1.player = m.playerA
JOIN players p2 ON p2.player_id = m.playerB
JOIN members mem2 ON mem2.player = m.playerB;

我希望它完成后看起来像这样。

    +-----------+------------------------------------+-----------+------------------------------------+--------+--------+
| player_id | real_name                          | player_id | real_name                          | scoreA | scoreB |  member1     member2
+-----------+------------------------------------+-----------+------------------------------------+--------+--------+
|        19 | Seong Hun Choi                     |         1 | Dong Nyoung Lee                    |      0 |      2 |
|        38 | Yun Sik Choi                       |         1 | Dong Nyoung Lee                    |      0 |      2 |
|       160 | Lei Wang                           |         1 | Dong Nyoung Lee                    |      0 |      2 |
|         3 | Seung Hyun Lee                     |         1 | Dong Nyoung Lee                    |      0 |      2 |
|        45 | Min Chul Kim                       |         1 | Dong Nyoung Lee                    |      0 |      2 |
|        23 | Sasha Hostyn                       |         1 | Dong Nyoung Lee                    |      2 |      4 |
|        52 | Juan Moreno Duran                  |         1 | Dong Nyoung Lee                    |      2 |      3 |
|        18 | Kang Dong Hyun                     |         1 | Dong Nyoung Lee                    |      2 |      1 |
|        22 | Jae Duck Lim                       |         1 | Dong Nyoung Lee                    |      1 |      2 |
|       108 | Jérémy Vansnick                  |         1 | Dong Nyoung Lee                    |      1 |      2 |
|        53 | Igor Turchin                       |         1 | Dong Nyoung Lee                    |      1 |      2 |
|        17 | Hyun Woo Park                      |         1 | Dong Nyoung Lee                    |      0 |      2 |

我还需要用另外两个表重复此过程，因此非常感谢帮助我理解如何执行此操作的过程。

---

匹配表。

+----------+------------+------------+---------+---------+--------+--------+---------+
| match_id | date       | tournament | playerA | playerB | scoreA | scoreB | offline |
+----------+------------+------------+---------+---------+--------+--------+---------+
|        1 | 2012-12-04 |        799 |       4 |      55 |      1 |      3 |       0 |
|        2 | 2012-12-03 |      11921 |       2 |      41 |      2 |      0 |       0 |
|        3 | 2012-12-03 |      11921 |      21 |      41 |      0 |      2 |       0 |
|        4 | 2012-12-03 |      11921 |       3 |       2 |      2 |      1 |       0 |
|        5 | 2012-12-03 |      11921 |      41 |       2 |      1 |      2 |       0 |
|        6 | 2012-12-03 |      11921 |      21 |       3 |      1 |      2 |       0 |
|        7 | 2012-12-03 |      11924 |       1 |       8 |      2 |      1 |       1 |
|        8 | 2012-12-03 |      11924 |       1 |       8 |      2 |      3 |       1 |
|        9 | 2012-12-03 |      11924 |       8 |      19 |      3 |      2 |       1 |
|       10 | 2012-12-03 |      11924 |      19 |      12 |      2 |      1 |       1 |
+----------+------------+------------+---------+---------+--------+--------+---------+

玩家表

+-----------+-----------+-----------------+-------------+------------+-----------+
| player_id | tag       | real_name       | nationality | birthday   | game_race |
+-----------+-----------+-----------------+-------------+------------+-----------+
|         1 | Leenock   | Dong Nyoung Lee | KR          | 1995-04-01 | Z         |
|         2 | Creator   | Hyun Woo Jang   | KR          | 1996-08-19 | P         |
|         3 | Life      | Seung Hyun Lee  | KR          | 1997-01-11 | Z         |
|         4 | DongRaeGu | Soo Ho Park     | KR          | 1991-06-03 | Z         |
|         5 | PartinG   | Lee Sak Won     | KR          | 1994-08-24 | P         |
|         6 | TaeJa     | Young Suh Yoon  | KR          | 1995-01-01 | T         |
|         7 | Rain      | Yoon Jong Jung  | KR          | 1992-08-14 | P         |
|         8 | viOLet    | Dong Hwan Kim   | KR          | 1990-12-05 | Z         |
|         9 | Sniper    | Tae Hoon Kwon   | KR          | 1995-01-22 | Z         |
|        10 | Stephano  | Ilyes Satouri   | FR          | 1993-03-12 | Z         |
+-----------+-----------+-----------------+-------------+------------+-----------+

成员表

+--------+------+------------+------------+
| player | team | start_date | end_date   |
+--------+------+------------+------------+
|      1 |   88 | 2011-02-23 | 2011-07-18 |
|      1 |    4 | 2011-07-18 | NULL       |
|      2 |    3 | 2011-01-12 | NULL       |
|      3 |   65 | 2010-10-19 | 2012-07-13 |
|      3 |    1 | 2012-07-13 | NULL       |
|      4 |    7 | 2011-02-20 | NULL       |
|      5 |   88 | 2010-10-29 | 2011-07-18 |
|      5 |    1 | 2012-10-21 | 2013-01-03 |
|      5 |   12 | 2013-01-14 | NULL       |
|      6 |   65 | 2011-03-01 | 2011-05-01 |
+--------+------+------------+------------+

Answer 1

看起来查询正在生成半笛卡尔积。

player 列在 member 表中不是唯一的。根据 start_date 和 end_date，看起来 player 一次只能是一个团队的 member .

我们可能希望将 match 表中的 date 与 start_date 和 end_date 进行比较在 member 表中。我们可能还希望将其设为外部联接。

LEFT
JOIN `member` mem2
  ON mem2.player      = m.playerB
 AND mem2.start_date <= m.date
 AND ( mem2.end_date >  m.date OR mem2.end_date IS NULL )

请注意，其中一个日期比较包括相等性，另一侧不包括(因为行上的 start_date 似乎等于另一行上的 end_date在 member 中，对于相同的 player。

(对于一场比赛 date='2011-07-18'，player=1 是哪支球队的成员？)

我们还想处理 end_date 中的 NULL 值，在这种情况下，它代表一个大于 match 的大值> 日期。

我们也想对 mem1 使用相同的模式。