MySQL 具有多个条件的多重联接并忽略基于其他行的行

Question

我正在尝试编写一个查询，该查询将向我显示我可能联合起来攻击特定机器人的所有 robots_partners。

鉴于上面的图像，

案例 1:我需要能够看到，克里斯控制瓦力，克里斯和瓦力的目标是 R2D2，迈克控制本德，迈克和本德也瞄准 R2D2，而且瓦力和本德是伙伴。这表明 Kris 和 Mike 是攻击 R2D2 的好队友。

案例 2:我还需要能够看到这一点，克里斯控制着 Wall-E，Kris 和 Wall-E 的目标是 GlaDOS，而 Bender 的目标是 GlaDOS，而 Bender 是 Wall-E 的合作伙伴。在这种情况下，没有人控制 Bender 并同时以 GlaDOS 为目标，这意味着我最好找到一个控制 Bender 并与我一起以 GlaDOS 为目标的人。

案例 3:我还需要能够看到，克里斯控制瓦力，克里斯和瓦力目标罗西。瓦力和本德是搭档，但本德并不针对罗西。

我不关心 Wall-E 的目标是擎天柱这一事实，因为克里斯的目标不是擎天柱。

现在，如果你和我待了这么久，我会将该查询写入到这样一个点:我可以将 INSERT SELECT 插入到我的表中，并让我的应用程序在从该表读取数据后进行一些清理，然后再传递结果关闭给用户。我需要帮助来获得最后一点，这样应用程序除了格式化结果之外不需要做任何事情。

这是我现在的查询，

SELECT DISTINCT pt.person_id AS person_1, rt.robot_1 AS robot_1, rt.robot_2 AS target, rp.robot_2 AS robot_2, IFNULL(pt2.person_id, 0) AS person_2
FROM robot_partners AS rp
JOIN robot_targets AS rt ON (rp.robot_1 = rt.robot_1)
LEFT JOIN robot_targets AS rt2 ON (rt.robot_2 = rt2.robot_2 And rp.robot_2 = rt2.robot_1)
JOIN people AS p ON (p.robot_id = rt.robot_1)
JOIN person_targets AS pt ON (p.person_id = pt.person_id And pt.robot_id = rt.robot_2)
LEFT JOIN people AS p2 ON (rt2.robot_1 = p2.robot_id)
LEFT JOIN person_targets AS pt2 ON (p2.person_id = pt2.person_id And rt2.robot_2 = pt2.robot_id)
ORDER BY pt.person_id, rt.robot_1, rt.robot_2, rp.robot_2;

此查询产生如下输出，

|person_1 | robot_1 | target | robot_2 | person_2 |
|   1     |    1    |   3    |     2   |    0     |
|   1     |    1    |   3    |     2   |    2     |
|   1     |    1    |   3    |     11  |    0     |
|   1     |    1    |   4    |     2   |    3     |
|   1     |    1    |   4    |     2   |    0     |
|   1     |    1    |   4    |     11  |    0     |
|   1     |    1    |   5    |     2   |    3     |
|   1     |    1    |   5    |     2   |    4     |
|   1     |    1    |   5    |     2   |    0     |
|   1     |    1    |   5    |     11  |    0     |
|   1     |    1    |   6    |     2   |    0     |
|   1     |    1    |   6    |     11  |    0     |
|   1     |    1    |   7    |     2   |    0     |
|   1     |    1    |   7    |     11  |    0     |
|   1     |    1    |   8    |     2   |    0     |
|   1     |    1    |   8    |     11  |    0     |
|   1     |    1    |   9    |     2   |    0     |
|   1     |    1    |   9    |     11  |    0     |
|   1     |    1    |   12   |     2   |    0     |
|   1     |    1    |   12   |     11  |    5     |
|   2     |    2    |   3    |     1   |    1     |
|   3     |    2    |   4    |     1   |    1     |
|   4     |    2    |   5    |     1   |    1     |
|   5     |    11   |   12   |     1   |    1     |

它依赖 DISTINCT 来删除一堆重复项，我不知道如何通过重新排列查询来删除这些重复项。当对于截至该点的完全相同的连接流也存在具有 person_2 id 的行时，它还会返回具有 0 person_2 的行。

例如:

|person_1 | robot_1 | target | robot_2 | person_2 |
|   1     |    1    |   5    |     2   |    4     |
|   1     |    1    |   5    |     2   |    0     |

在这种情况下，我不关心 person_2 = 0 的行，因为 person_2 = 4 的行在那里，并且它们具有相同的路径 (1,1,5,2,X)。

我无法使用子查询 ORDER BY DESC/GROUP BY 来摆脱它，因为当这种情况发生时，

|person_1 | robot_1 | target | robot_2 | person_2 |
|   1     |    1    |   5    |     2   |    3     |
|   1     |    1    |   5    |     2   |    4     |
|   1     |    1    |   5    |     2   |    0     |

我需要能够看到第 3 个人和第 4 个人有联系，并使用 GROUP BY 来删除此信息。

但是，我不能忽略 person_2 = 0 的所有行，因为我需要知道情况 2 何时适用。

至于为什么person_2会从NULL转为0，是因为该数据所在的表有一个主键涉及全部5列，并且PK不能为NULL。我也不能将其设为唯一键，因为当我执行 INSERT INTO SELECT... 时，它会不断添加重复行，因为 NULL != NULL。此外，所有 5 列都对其各自的表具有外键约束。 0 起作用的原因是因为我在 people 表中有一个虚拟人 0。

所以我理想的输出是这样的，

|person_1 | robot_1 | target | robot_2 | person_2 |
|   1     |    1    |   3    |     2   |    2     |
|   1     |    1    |   3    |     11  |    0     |
|   1     |    1    |   4    |     2   |    3     |
|   1     |    1    |   4    |     11  |    0     |
|   1     |    1    |   5    |     2   |    4     |
|   1     |    1    |   5    |     2   |    3     |
|   1     |    1    |   5    |     11  |    0     |
|   1     |    1    |   6    |     2   |    0     |
|   1     |    1    |   6    |     11  |    0     |
|   1     |    1    |   7    |     2   |    0     |
|   1     |    1    |   7    |     11  |    0     |
|   1     |    1    |   8    |     2   |    0     |
|   1     |    1    |   8    |     11  |    0     |
|   1     |    1    |   9    |     2   |    0     |
|   1     |    1    |   9    |     11  |    0     |
|   1     |    1    |   12   |     2   |    0     |
|   1     |    1    |   12   |     11  |    5     |
|   2     |    2    |   3    |     1   |    1     |
|   3     |    2    |   4    |     1   |    1     |
|   4     |    2    |   5    |     1   |    1     |
|   5     |    11   |   12   |     1   |    1     |

这 3 行已从结果中删除。

|   1     |    1    |   3    |     2   |    0     |
|   1     |    1    |   4    |     2   |    0     |
|   1     |    1    |   5    |     2   |    0     |

一切设置齐全的游戏区。类似于 sqlfiddle。

http://rextester.com/MQCZX67915

这里是本示例中使用的所有表格，我还提供了一个 rextester 链接。生成所有这些的代码也在 rextester 链接中。我本来会执行 sqlfiddle 但它拒绝运行查询。

感谢您的任何帮助或想法。

人物:

+-----------+--------+----------+
| person_id |  name  | robot_id |
+-----------+--------+----------+
|         1 | Kris   |        1 |
|         2 | Mike   |        2 |
|         3 | Sue    |        2 |
|         4 | Dora   |        2 |
|         5 | Walter |       11 |
+-----------+--------+----------+

机器人:

+----------+----------------+
| robot_id |      name      |
+----------+----------------+
|        1 | Wall-E         |
|        2 | Bender         |
|        3 | R2D2           |
|        4 | Data           |
|        5 | HAL 9000       |
|        6 | GlaDOS         |
|        7 | ASIMO          |
|        8 | The Terminator |
|        9 | Rosie          |
|       10 | Optimus Prime  |
|       11 | The Iron Giant |
|       12 | Clank          |
+----------+----------------+

机器人合作伙伴:

+------------------+---------+---------+
| robot_partner_id | Robot_1 | Robot_2 |
+------------------+---------+---------+
|                1 |       1 |       2 |
|                2 |       2 |       1 |
|                3 |       1 |      11 |
|                4 |      11 |       1 |
+------------------+---------+---------+

人物目标:

+------------------+-----------+----------+
| person_target_id | person_id | robot_id |
+------------------+-----------+----------+
|                1 |         1 |        3 |
|                2 |         1 |        4 |
|                3 |         1 |        5 |
|                4 |         1 |        6 |
|                5 |         1 |        7 |
|                6 |         1 |        8 |
|                7 |         1 |        9 |
|                8 |         1 |       12 |
|                9 |         2 |        3 |
|               10 |         3 |        4 |
|               11 |         4 |        5 |
|               12 |         5 |       12 |
|               13 |         3 |        5 |
+------------------+-----------+----------+

机器人目标:

+-----------------+---------+---------+
| robot_target_id | Robot_1 | Robot_2 |
+-----------------+---------+---------+
|               1 |       1 |       3 |
|               2 |       1 |       4 |
|               3 |       1 |       5 |
|               4 |       1 |       6 |
|               5 |       1 |       7 |
|               6 |       1 |       8 |
|               7 |       1 |       9 |
|               8 |       1 |      10 |
|               9 |       1 |      12 |
|              10 |       2 |       3 |
|              11 |       2 |       4 |
|              12 |       2 |       5 |
|              13 |       2 |       6 |
|              14 |       2 |       7 |
|              15 |       2 |       8 |
|              16 |      11 |      12 |
+-----------------+---------+---------+

Answer 1

对具有人员 2 的所有组合进行 UNION，并与具有或不具有人员 2 的所有组合相结合，但无论如何，前四个部分不在第一个列表中。请注意，需要 CONCAT_WS 来区分 1 + 23 和 12 + 3 之间的差异，这两者都会连接到 123 - 这使它们成为 1~23 和 12~3。

(SELECT DISTINCT pt.person_id AS person_1, rt.robot_1 AS robot_1, rt.robot_2 AS target, rp.robot_2 AS robot_2, pt2.person_id AS person_2

FROM robot_partners AS rp

JOIN robot_targets AS rt ON (rp.robot_1 = rt.robot_1)

JOIN robot_targets AS rt2 ON (rt.robot_2 = rt2.robot_2 And rp.robot_2 = rt2.robot_1)

JOIN people AS p ON (p.robot_id = rt.robot_1)

JOIN person_targets AS pt ON (p.person_id = pt.person_id And pt.robot_id = rt.robot_2)

JOIN people AS p2 ON (rt2.robot_1 = p2.robot_id)

JOIN person_targets AS pt2 ON (p2.person_id = pt2.person_id And rt2.robot_2 = pt2.robot_id))

UNION

(SELECT DISTINCT pt.person_id AS person_1, rt.robot_1 AS robot_1, rt.robot_2 AS target, rp.robot_2 AS robot_2, IFNULL(pt2.person_id, 0) AS person_2

FROM robot_partners AS rp

JOIN robot_targets AS rt ON (rp.robot_1 = rt.robot_1)

LEFT JOIN robot_targets AS rt2 ON (rt.robot_2 = rt2.robot_2 And rp.robot_2 = rt2.robot_1)

JOIN people AS p ON (p.robot_id = rt.robot_1)

JOIN person_targets AS pt ON (p.person_id = pt.person_id And pt.robot_id = rt.robot_2)

LEFT JOIN people AS p2 ON (rt2.robot_1 = p2.robot_id)

LEFT JOIN person_targets AS pt2 ON (p2.person_id = pt2.person_id And rt2.robot_2 = pt2.robot_id)

WHERE CONCAT_WS('~',pt.person_id, rt.robot_1, rt.robot_2, rp.robot_2)

    NOT IN 

       (SELECT DISTINCT CONCAT_WS('~',pt.person_id, rt.robot_1, rt.robot_2, rp.robot_2)

       FROM robot_partners AS rp

       JOIN robot_targets AS rt ON (rp.robot_1 = rt.robot_1)

       JOIN robot_targets AS rt2 ON (rt.robot_2 = rt2.robot_2 And rp.robot_2 = rt2.robot_1)

       JOIN people AS p ON (p.robot_id = rt.robot_1)

       JOIN person_targets AS pt ON (p.person_id = pt.person_id And pt.robot_id = rt.robot_2)

       JOIN people AS p2 ON (rt2.robot_1 = p2.robot_id)

       JOIN person_targets AS pt2 ON (p2.person_id = pt2.person_id And rt2.robot_2 = pt2.robot_id))

 )

ORDER BY person_1, robot_1, target, robot_2;