sql - mySQL SELECT连接，分组依据

我不能在这个正确的轨道上，任何帮助将不胜感激

我有一张桌子

+---+----------+---------+-----------+
|id | match_id | team_id | player_id |
+---+----------+---------+-----------+
| 1 |        9 |      10 |         5 |
| 2 |        9 |      10 |         7 |
| 3 |        9 |      10 |         9 |
| 4 |        9 |      11 |        12 |
| 5 |        9 |      11 |        15 |
| 6 |        9 |      11 |        18 |
+---+----------+---------+-----------+

我想在match_id和两个队的id上选择一个位置，以便输出为

+---------+-------+------+---------+---------+
| MATCHID | TEAMA | TEAMB| PLAYERA | PLAYERB |
+---------+-------+------+---------+---------+
|       9 |    10 |   11 |       5 |      12 |
|       9 |    10 |   11 |       7 |      15 |
|       9 |    10 |   11 |       9 |      18 |
+---------+-------+------+---------+---------+

这可能很简单，但是我被卡住了。

提前致谢

ps。似乎忘了我第一篇文章的专栏，对不起

最佳答案

我认为您需要：

SELECT
    a.match_id, a.team_id AS TeamA, b.team_id AS teamB,
    a.player_id AS PlayerA, b.player_id AS PlayerB
FROM PLayer AS a
    INNER JOIN Player AS b ON a.match_id = b.match_id
WHERE a.team_id < b.team_id

尽管这将为您提供每场比赛的每一对玩家，即

+---------+-------+------+---------+---------+
| MATCHID | TEAMA | TEAMB| PLAYERA | PLAYERB |
+---------+-------+------+---------+---------+
|       9 |    10 |   11 |       5 |      12 |
|       9 |    10 |   11 |       5 |      15 |
|       9 |    10 |   11 |       5 |      18 |
|       9 |    10 |   11 |       7 |      12 |
|       9 |    10 |   11 |       7 |      15 |
|       9 |    10 |   11 |       7 |      18 |
|       9 |    10 |   11 |       9 |      12 |
|       9 |    10 |   11 |       9 |      15 |
|       9 |    10 |   11 |       9 |      18 |
+---------+-------+------+---------+---------+

为了进一步限制它，您需要一个标准来确定玩家应该配对。

关于sql - mySQL SELECT连接，分组依据，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/1893525/