消除具有反向关系且无主键的元组

我试图根据下面的问题插入所有关系。我已经得到了从 A 到 C 的所有关系（根据下面的问题）。但就我而言，我还得到了“C 与 A 的朋友”的记录。据我理解问题陈述“没有重复的友谊”，我必须将友谊插入为 A 到 C，而不是反之亦然。要么我对问题理解错误，要么得不到想要的结果。
因此，当我尝试将我得到的所有值插入表中时，我的结果是错误的。有些人的朋友数量超出了他们的期望，数量为 2。

表的结构如下：

Friend ( ID1, ID2 )

ID1 的学生与 ID2 的学生是朋友。友谊是相互的，因此如果 (123, 456) 在 Friend 表中，那么 (456, 123) 也在。

（无主键） 我试图解决的情况是：

“对于 A 与 B 是朋友、B 与 C 是朋友的所有情况，请为 A 和 C 对添加新的友谊。不要添加重复的友谊、已经存在的友谊或与自己的友谊。”

我已经尝试解决这个问题两天了。请帮忙。

提前致谢。

----我的SQL查询-----

select B.ID1 as ID1,B.ID3 as ID2
from (select A.ID1 as ID1,A.ID2 as ID2,A.ID3 as ID3,F3.ID2 as ID4
from (select F1.ID1 as ID1,F1.ID2 as ID2,F2.ID2 as ID3
from Friend F1 join Friend F2
on F1.ID2=F2.ID1
where F1.ID1<>F2.ID2) A join Friend F3
on A.ID3=F3.ID1) B
where B.ID1<>B.ID4
group by  B.ID1,B.ID3

我们假设（对于示例）朋友表包含这些行。

ID1 ID2
--- ---
 a   b
 a   c
 b   a
 b   c
 b   d
 c   b

首先，从识别朋友表中的“完整朋友”元组开始，使用如下查询：

SELECT fa.ID1
     , fa.ID2
  FROM friend fa
  JOIN friend fb
    ON fb.ID1 = fa.ID2
   AND fb.ID2 = fa.ID1

fa.ID1 fa.ID2
------ ------
  a      b
  b      a
  b      c
  c      b

这个结果告诉我们a是b的朋友，b是c的朋友。这(a,c) and (b,d)行被省略，因为没有逆，(c,a) or (d,b).

目前，我们将此集合称为“ft“（朋友元组）。现在我们可以针对该集合（ft）编写一个查询，以获取所有“a->b->c”和“c->b->a”朋友对。

SELECT fx.ID1
     , fy.ID2
  FROM ft fx
  JOIN ft fy
    ON fy.ID1 = fx.ID2 
   AND fy.ID2 <> fx.ID1

fx.ID1 fy.ID2
------ ------
  a      c
  c      a

但是，我们需要确保不会重复友元表中已有的任何行，因此我们可以使用 NOT IN 或 NOT EXISTS 谓词，或者我们可以使用反连接模式来消除匹配好友表中已有的一行。

SELECT fx.ID1
     , fy.ID2
  FROM ft fx
  JOIN ft fy
    ON fy.ID1 = fx.ID2 
   AND fy.ID2 <> fx.ID1
  -- eliminate rows that match
  LEFT
  JOIN friend fe
    ON fe.ID1 = fx.ID1
   AND fe.ID2 = fy.ID2
 WHERE fe.ID1 IS NULL

fx.ID1 fy.ID2
------ ------
  c      a

现在，我们可以将引用替换为ft使用生成该集合的查询（作为内联视图）：

SELECT fx.ID1
     , fy.ID2
  FROM ( SELECT fa.ID1
              , fa.ID2
           FROM friend fa
           JOIN friend fb
             ON fb.ID1 = fa.ID2
            AND fb.ID2 = fa.ID1
       ) fx
  JOIN ( SELECT fc.ID1
              , fc.ID2
           FROM friend fc
           JOIN friend fd
             ON fd.ID1 = fc.ID2
            AND fd.ID2 = fc.ID1
       ) fy
    ON fy.ID1 = fx.ID2 
   AND fy.ID2 <> fx.ID1
  -- eliminate rows that match
  LEFT
  JOIN friend fe
    ON fe.ID1 = fx.ID1
   AND fe.ID2 = fy.ID2
 WHERE fe.ID1 IS NULL
 GROUP 
    BY fx.ID1
     , fy.ID2

（我想只要我们保证 (ID1,ID2) 是唯一的，这个查询就不会生成任何重复项。而且我认为这个查询只会生成指定的匹配项，而不是任何额外的匹配项.一些额外的测试用例是为了确认。如果查询确实产生任何重复项，则添加一个GROUP BY fx.ID1, fy.ID2查询会消除它们。）

最后，要将这些行放入朋友表中，请在查询之前添加：

INSERT INTO friend (ID1,ID2)

UPDATE

我们想要返回的结果实际上取决于如何表示“友谊”。

我假设“朋友”对出现在friend表通过两个元组的存在：两者(a,b)(b,a) 必须存在。 （当“a 朋友 b”和“b 朋友 a”时，就形成了友谊）。

如果只有一行存在，那不是真正的友谊，只是半途而废的友谊。

我运行了几个测试用例。通过它们进行工作有点乏味。我通过添加 ORDER BY 来扩展查询，以按确定的顺序返回行，并在 SELECT 列表中添加其他列，以验证“路径”（共享朋友）。我注释掉了 WHERE 子句，这样我就可以看到所有潜在的朋友。

我确实发现我需要添加一个GROUP BY以消除重复项。我们可以推导出a-c来自两个或多个共同朋友的友谊，例如b and r. Both a-b + b-c and a-r + r-c导致a-c.

这是我测试的最终查询。除了添加了 GROUP BY 之外，它本质上与前面的相同。

SELECT fx.ID1
     , fy.ID2
 --  , fx.ID1>fy.ID2 AS d
 --  , fx.ID1 AS x1
 --  , fx.ID2 As x2
 --  , fy.ID1 AS y1
 --  , fy.ID2 As y2
 --  , fe.ID1 AS e1
 --  , fe.ID2 AS e2
  FROM ( SELECT fa.ID1
              , fa.ID2
              , fa.ID1>fa.ID2 AS d
           FROM friend fa
           JOIN friend fb
             ON fb.ID1 = fa.ID2
            AND fb.ID2 = fa.ID1
       -- ORDER
       --    BY LEAST(fa.ID1,fa.ID2)
       --     , GREATEST(fa.ID1,fa.ID2)
       --     , fa.ID1>fa.ID2
       ) fx
  JOIN ( SELECT fc.ID1
              , fc.ID2
           FROM friend fc
           JOIN friend fd
             ON fd.ID1 = fc.ID2
            AND fd.ID2 = fc.ID1
       -- ORDER
       --    BY LEAST(fc.ID1,fc.ID2)
       --     , GREATEST(fc.ID1,fc.ID2)
       --     , fc.ID1>fc.ID2
       ) fy
    ON fy.ID1 = fx.ID2 
   AND fy.ID2 <> fx.ID1
  -- eliminate rows that match existing row
  LEFT
  JOIN friend fe
    ON fe.ID1 = fx.ID1
   AND fe.ID2 = fy.ID2
 WHERE fe.ID1 IS NULL
 GROUP
    BY fx.ID1
     , fy.ID2
 ORDER
    BY LEAST(fx.ID1,fy.ID2)
     , GREATEST(fx.ID1,fy.ID2)
     , fx.ID1>fy.ID2

如果仅存在一个元组“(a,b)”来表示完整的友谊，则意味着“(b,a)”，则需要更改查询。

内联视图查询fx and fy需要扩展以返回“丢失的”逆元组...如果 (a,b) 在朋友表中，我们的查询需要返回 (a,b) 和 (b,a)。我们可以通过在两个相同的查询之间执行 UNION ALL 操作来实现这一点，只是颠倒 SELECT 列表中列的顺序。 （在这里，我们实际上可以使用 UNION 而不是 UNION ALL 来消除任何重复项。）内联视图查询fx and fy会是这样的：

SELECT fa.ID1, fa.ID2 FROM ...
 UNION ALL
SELECT fa.ID2, fa.ID1 FROM ...

还需要更改好友表中消除匹配行的检查（如果我们发现现有的 (a,b) 或 (，我们希望从结果集中消除 (a,b) 和 (b,a) b,a) 行)

ON ( fe.ID1 = fx.ID1 AND fe.ID2 = fy.ID2 )
OR ( fe.ID1 = fy.ID2 AND fe.ID2 = fx.ID1 )

并且需要更改 SELECT 列表和 GROUP BY 以消除“额外的”逆元组。我们可以使用 ORDER BY 中的表达式

SELECT LEAST(fx.ID1,fy.ID2) AS ID1
     , GREATEST(fx.ID1,fy.ID2) AS ID2
       ...
 GROUP
    BY LEAST(fx.ID1,fy.ID2)
     , GREATEST(fx.ID1,fy.ID2)