mysql - 如何 "reverse"一个简单的 2 列关系表

Question

晚上好

我是 SQL 新手。

我一直在尝试“反转”一个简单的 2 列关系表。

下面是一个例子来解释它:

表认识

¦     id_human1   ¦     id_human2      ¦
¦        1        ¦         10         ¦
¦        1        ¦         11         ¦
¦        2        ¦         12         ¦
¦        2        ¦         13         ¦

所以这里我有一张互相认识的人的表格。每行表示 humanX 和 humanY 彼此认识。现在我想得到一张互不认识的人的 table (假设这张 table 上有所有人)。这会给这个:

表不认识

¦     id_human1   ¦     id_human2      ¦
¦        1        ¦          2         ¦
¦        1        ¦         12         ¦
¦        1        ¦         13         ¦
¦        2        ¦         10         ¦
¦        2        ¦         11         ¦
¦        11       ¦         10         ¦
¦        12       ¦         10         ¦
¦        13       ¦         10         ¦
¦        11       ¦         12         ¦
¦        11       ¦         13         ¦
¦        12       ¦         13         ¦

任何关于如何做到这一点的提示将不胜感激。更令人感激的是你如何处理这样重要的任务。努力在这里变得更好 ;)。

非常感谢

Answer 1

首先，让我们从您忘记的事情开始。 (如果你真的不想有这个，它可以从你给的表格中计算出来，但是有它肯定更好。)

CREATE TABLE Humans 
  (
    id int PRIMARY KEY
  );

INSERT INTO Humans (id) VALUES (1),(2),(11),(12),(13);

然后，这是您的表和数据:

CREATE TABLE KnowEachOther 
  (
    id1 INT,
    id2 INT,
    PRIMARY KEY ( id1, id2 )
  );

INSERT INTO KnowEachOther (id1, id2) VALUES
 (1,10),
 (1,11),
 (2,12),
 (2,13);

然后，我们可以声明以下所有可能关系的非常有用的 View :

CREATE VIEW AllPossibleRelationships AS SELECT
    h1.id AS id1,
    h2.id AS id2
FROM Humans AS h1 
    CROSS JOIN Humans AS h2
WHERE h1.id <> h2.id;

然后，我们可以创建一个 View ，从“所有可能的关系”中删除那些存在关系的行。 (参见 WHERE k.id1 IS NULL)

CREATE VIEW DontKnowEachOther AS SELECT 
    a.id1 AS id1, 
    a.id2 AS id2
FROM AllPossibleRelationships AS a 
    LEFT JOIN KnowEachOther AS k
        ON (a.id1 = k.id1 AND a.id2 = k.id2) OR 
           (a.id1 = k.id2 AND a.id2 = k.id1)
WHERE k.id1 IS NULL
ORDER BY a.id1;

因此，执行 SELECT * FROM DontKnowEachOther; 会产生以下结果:

id1 id2
1   2
1   12
1   13
2   1
2   11
11  2
11  12
11  13
12  1
12  11
12  13
13  1
13  11
13  12

注意:关于您的 KnowEachOther 表的内容以及“相互了解”的含义，存在一些歧义。 “互相认识”是一种无向关系，这意味着如果 A 认识 B，那么 B 也认识 A。鉴于此，您的“互相认识”表可以被认为隐式包含更多行；例如，由于您有 (1, 10) 行，因此隐含行 (10, 1)。我的结果考虑了这些隐含的行，并在结果中包括了所有隐含的和非隐含的行。

过滤掉可能隐含的行作为练习留给读者。