mysql - 从多个 LEFT JOIN 获取不同的行

Question

这是我的匹配表

+----------------------------+
|Match_id |team1_id|team2_id |
------------------------------
|    1    |   1    |    2    |
+----------------------------+

球队有几场比赛由 team_player 映射

+------------------+
|team_id |player_id|
--------------------
|    1    |   1    |
|    1    |   2    |
|    1    |   3    |
|    2    |   4    |
|    2    |   5    |
|    2    |   6    |
+------------------+

然后是players表

+----------------------+
|player_id |player_name|
-----------------------|
|    1     |   p1      |
|    2     |   p2      |
|    3     |   p3      |
|    4     |   p4      |
|    5     |   p5      |
|    6     |   p6      |
+----------------------+

我尝试按球队获取球员姓名。这是我的查询:

SELECT tp1.`player_id` as id1, tp2.`player_id` as id2, p1.`player_name` as pseudo1, p2.`player_name` as pseudo2
FROM (`match` m
LEFT JOIN `team_player` tp1 ON m.`team1_id` = tp1.`team_id`
LEFT JOIN `team_player` tp2 ON m.`team2_id` = tp2.`team_id`)
LEFT JOIN `players` p1 ON tp1.`player_id` = p1.`player_id`
LEFT JOIN `players` p2 ON tp2.`player_id` = p2.`player_id`
WHERE m.`MATCH_ID`= 49

这让我明白了:

+------------------------+
|id1 |id2|pseudo1|pseudo2|
|------------------------|
|1   |4  |   p1  | p4    |
|1   |5  |   p1  | p5    |
|1   |6  |   p1  | p6    |
|2   |4  |   p2  | p4    |
|2   |5  |   p2  | p5    |
|2   |6  |   p2  | p6    |
|3   |4  |   p3  | p4    |
|3   |5  |   p3  | p5    |
|3   |6  |   p3  | p6    |
+------------------------+

是否可以得到这个:

+------------------------+
|id1 |id2|pseudo1|pseudo2|
|------------------------|
|1   |4  |   p1  | p4    |
|3   |5  |   p2  | p5    |
|1   |6  |   p3  | p6    |
+------------------------+

谢谢;)

Answer 1

这是一个不平凡的结果。我更愿意使用两个单独的语句来获取两个单独的团队列表，并在客户端中一起处理两个结果的格式，而不是使用单个 SQL 语句。

但是，如果我必须使用单个 SQL 语句生成此结果，我会这样做，使用内联 View ，使用用户定义的变量为每个团队列表生成“行号”值...使用 UNION ALL 列出两个团队列表，并使用 GROUP BY 行号来组合行，并使用 SELECT 列表中的条件来挑选 team1 和 team2 列的值。

   SELECT MAX(IF(s.tn='1',s.id    ,NULL)) AS id1
        , MAX(IF(s.tn='2',s.id    ,NULL)) AS id2
        , MAX(IF(s.tn='1',s.pseudo,NULL)) AS pseudo1
        , MAX(IF(s.tn='2',s.pseudo,NULL)) AS pseudo2
     FROM ( 
            ( SELECT @rn1 := @rn1 + 1 AS rn
                   , '1'              AS tn
                   , tp1.player_id    AS id
                   , p1.player_name   AS pseudo
                FROM (SELECT @rn1 := 0 ) i1 
                JOIN `match` m1 ON m1.match_id   = 49
                JOIN team_player tp1 ON tp1.team_id = m1.team1_id
                LEFT JOIN players p1 ON p1.player_id = tp1.player_id 
               ORDER BY p1.player_id
            )  
            UNION ALL    
            ( SELECT @rn2 := @rn2 + 1 AS rn
                   , '2'              AS tn
                   , tp2.player_id    AS id
                   , p2.player_name   AS pseudo
                FROM (SELECT @rn2 := 0 ) i2 
                JOIN `match` m2 ON m2.match_id   = 49
                JOIN team_player tp2 ON tp2.team_id = m2.team2_id
                LEFT JOIN players p2 ON p2.player_id = tp2.player_id 
               ORDER BY p2.player_id
            ) 
          ) s
    GROUP BY s.rn
    ORDER BY s.rn

SQL Fiddle demomonstration here: http://sqlfiddle.com/#!9/61dd4/2