MySQL 查询对

Question

如何选择所有尚未与“alpha”配对的玩家？ (输出=' Gamma ')。即使配对表为空，它也应该工作。

table: players
+----------+-------+
| playerID | name  |
+----------+-------+
|        1 | alpha |
|        2 | beta  |
|        3 | gamma |
+----------+-------+

table: pairs
+---------+---------+
| player1 | player2 |
+---------+---------+
|       2 |       3 |
|       1 |       2 |
+---------+---------+

我现在已经挣扎了几个小时。例如如果我这样做

SELECT p.*, r.*
FROM players p
JOIN pairs r
    ON (player1 = playerID) OR (player2 = playerID)
WHERE
    ((r.player1 != 1) AND (r.player2 != 1));

输出是“beta”和“gamma”。在连接中，“beta”出现两次(每次与“alpha”和“gamma”配对)。 WHERE 条件消除了一行“beta”。我想要的是消除所有行的“测试版”。我是新手，尝试了 GROUP BY、HAVING 等的各种组合。我无法让它工作。

SQL Fiddle here.

Answer 1

我会将其分解成更小的部分并将其拼凑在一起。首先获取 alpha 玩家的 ID:

SELECT playerID
FROM players
WHERE name = 'alpha';

下一步是弄清楚如何排除。我会得到与玩家 1 配对的所有玩家的列表。我通过选择所有玩家 1 为“alpha”的玩家 2 值和所有玩家 2 为“alpha”的玩家 1 值来做到这一点，如下所示:

SELECT p.player2
FROM pairs p
JOIN(
  SELECT playerID
  FROM players
  WHERE name = 'alpha') b ON b.playerID = p.player1
UNION
SELECT p.player1
FROM pairs p
JOIN(
  SELECT playerID
  FROM players
  WHERE name = 'alpha') b ON b.playerID = p.player2;

完成此操作后，剩下的唯一步骤就是从 1) 不是 alpha 且 2) 不在上述列表中的玩家中拉出:

SELECT *
FROM players
WHERE playerID NOT IN(
  SELECT playerID
  FROM players
  WHERE name = 'alpha') 
AND playerID NOT IN(
  SELECT p.player2
  FROM pairs p
  JOIN(
    SELECT playerID
    FROM players
    WHERE name = 'alpha') b ON b.playerID = p.player1
  UNION
  SELECT p.player1
  FROM pairs p
  JOIN(
    SELECT playerID
    FROM players
    WHERE name = 'alpha') b ON b.playerID = p.player2);

SQL Fiddle这里的例子。