mysql - 如果表 2 中不存在对，则从表 1 中选择 2 行(使用 UNION)

Question

所以我有两个表:

表uno:

id      |  gender  |   lf 
-------------------------
abc   |       1      |    2
cde   |       2      |    1
efg   |       1      |    2

table 二人组:

id1    |    id2 
-------------------------
abc   |   cde

我的工作(到目前为止)查询从表 uno 中恰好选择了 2 行:

(SELECT 
  * 
FROM
  uno 
WHERE gender = 1 
  AND lf = 2 
ORDER BY RAND() 
LIMIT 1) 
UNION
(SELECT 
  * 
FROM
  uno 
WHERE gender = 2 
  AND lf = 1 
ORDER BY RAND() 
LIMIT 1)

哪个返回:

id    |   gender     | lf
-------------------------
abc   |       1      |    2
cde   |       2      |    1

我需要的(并且还没有弄清楚是否可以通过查询实现)是返回 2 个未在表 duo 中配对的 ID。

在此示例中，上述查询不应返回 abc 和 cde，因为它们已经在表 duo 中(可能的对是 efg 和 abc，efg 和 cde，因为它们在 table duo 中找不到)。

谢谢!

更新:在 chiliNUT 的帮助下，我得到了这个:

(SELECT 
  id,gender,lf 
FROM
  uno u1 
WHERE NOT EXISTS 
  /* id1-centric exclusion rule  */
  /* look at id1 FROM duo, exclude it IF it IS IN uno AND id2 IS also IN uno */
  (SELECT 
    d.id1,
    d.id2,
    u2.id 
  FROM
    duo d 
    LEFT JOIN uno u2 /* note the JOIN order, duo on uno */
      ON d.id2 = u2.id 
  WHERE d.id1 = u1.id) 
  /* id2-centric exclusion rule  */
  /* look at id1 FROM duo, exclude it IF it IS IN uno AND id2 IS also IN uno */
  AND NOT EXISTS 
  (SELECT 
    d.id1,
    d.id2,
    u2.id 
  FROM
    uno u2 
    LEFT JOIN duo d /* note the JOIN order, uno on duo */
      ON d.id1 = u2.id 
  WHERE d.id2 = u1.id) and  (gender=1 and lf=2)order by rand() limit 1)UNION(SELECT 
  id,gender,lf 
FROM
  uno u1 
WHERE NOT EXISTS 
  /* id1-centric exclusion rule  */
  /* look at id1 FROM duo, exclude it IF it IS IN uno AND id2 IS also IN uno */
  (SELECT 
    d.id1,
    d.id2,
    u2.id 
  FROM
    duo d 
    LEFT JOIN uno u2 /* note the JOIN order, duo on uno */
      ON d.id2 = u2.id 
  WHERE d.id1 = u1.id) 
  /* id2-centric exclusion rule  */
  /* look at id1 FROM duo, exclude it IF it IS IN uno AND id2 IS also IN uno */
  AND NOT EXISTS 
  (SELECT 
    d.id1,
    d.id2,
    u2.id 
  FROM
    uno u2 
    LEFT JOIN duo d /* note the JOIN order, uno on duo */
      ON d.id1 = u2.id 
  WHERE d.id2 = u1.id) and  (gender=2 and lf=1) order by rand() limit 1)

我必须进行编辑，以便提供 2 个 ID(具有不同的性别和 lf)。这肯定没有优化，可能会挂起我的数据库，但它是一个开始!谢谢 chiliNUT!

Answer 1

下面的查询将选择uno中的所有id，其中id不是id1,id2的成员 对。它查看 uno 中的每个 id，然后检查它是否与 duo.id1 匹配，如果匹配，它检查关联的 duo.id2 也在 uno 中。然后，在另一个方向上，它检查 uno 中的 id 是否与 duo.id2 匹配，然后检查 duo .id1 也在 uno 中。

SELECT 
  id 
FROM
  uno u1 
WHERE NOT EXISTS 
  /* id1-centric exclusion rule  */
  /* look at id1 FROM duo, exclude it if it is in uno AND id2 is also in uno */
  (SELECT 
    d.id1,
    d.id2,
    u2.id 
  FROM
    duo d 
    LEFT JOIN uno u2 /* note the JOIN order, duo on uno */
      ON d.id2 = u2.id 
  WHERE d.id1 = u1.id) 
  /* id2-centric exclusion rule  */
  /* look at id2 FROM duo, exclude it id it is in uno AND id1 is also in uno */
  AND NOT EXISTS 
  (SELECT 
    d.id1,
    d.id2,
    u2.id 
  FROM
    uno u2 
    LEFT JOIN duo d /* note the JOIN order, uno on duo */
      ON d.id1 = u2.id 
  WHERE d.id2 = u1.id)

|id |
+---+
|efg|

如果删除第二个 existance 子句，它将返回 cde，而不是 abc。如果删除第一个存在子句，它返回 abc，但不返回 cde。所以两者都需要。