sql - 如何使用 JOIN 而不是 UNION 来计算 "A OR B"的邻居？

Question

以下查询计算图中两个节点的公共(public)邻居:

    DECLARE @monthly_connections_test TABLE (
  calling_party VARCHAR(50)
  , called_party VARCHAR(50))

INSERT INTO @monthly_connections_test
          SELECT 'z1', 'z2'
UNION ALL SELECT 'z1', 'z3'
UNION ALL SELECT 'z1', 'z4'
UNION ALL SELECT 'z1', 'z5'
UNION ALL SELECT 'z1', 'z6'
UNION ALL SELECT 'z2', 'z1'
UNION ALL SELECT 'z2', 'z4'
UNION ALL SELECT 'z2', 'z5'
UNION ALL SELECT 'z2', 'z7'
UNION ALL SELECT 'z3', 'z1'
UNION ALL SELECT 'z4', 'z7'
UNION ALL SELECT 'z5', 'z1'
UNION ALL SELECT 'z5', 'z2'
UNION ALL SELECT 'z7', 'z4'
UNION ALL SELECT 'z7', 'z2'

SELECT     monthly_connections_test.calling_party AS user1, monthly_connections_test_1.calling_party AS user2, COUNT(*) AS calling_calling, 0 AS calling_called, 
                      0 AS called_calling, 0 AS called_called, 0 AS both_directions
FROM         @monthly_connections_test AS monthly_connections_test INNER JOIN
                      @monthly_connections_test AS monthly_connections_test_1 ON 
                      monthly_connections_test.called_party = monthly_connections_test_1.called_party AND 
                      monthly_connections_test.calling_party < monthly_connections_test_1.calling_party
GROUP BY monthly_connections_test.calling_party, monthly_connections_test_1.calling_party

对于下图


它返回由 user1 AND user2 调用的公共(public)邻居的数量，例如，由 z1 AND z2 调用的邻居的数量它返回 2 作为调用 z4 和 z5。

我想计算的另一件事是由 user1 或 user2 调用的两个节点(用户)的所有邻居的数量，例如对于(z1，z2)对，查询应该返回 5(用户 z1 调用 z2，z3 , z4, z5, z6 和用户 z2 调用 z1, z4, z5, z7 - z1 和 z2 之间的连接必须排除，因为 (z1, z2) 是观察到的对，并且 (z3, z4, z5 中的元素数, z6) U (z4, z5, z7) 是 5)。

有谁知道如何修改/创建上述逻辑的连接查询？

谢谢!

Answer 1

@Martin 的回答是正确的。他是个天才。

去马丁!

更正

如果针对我给出的双向解决方案运行，他的答案只需进行 1 次小修改即可。否则结果不正确。

所以你的回答是他的和我的:)

完整的解决方案:

DECLARE @T1 TABLE (calling_party VARCHAR(50), called_party VARCHAR(50))

INSERT  INTO @T1
SELECT  *
FROM    dbo.monthly_connections_test

INSERT  INTO @T1
SELECT  *
FROM    (
        SELECT  called_party AS calling_party, calling_party AS called_party
        FROM    dbo.monthly_connections_test AS T2
        WHERE   T2.called_party < T2.calling_party
        ) T2
WHERE   NOT EXISTS (
        SELECT *
        FROM    monthly_connections_test
        WHERE   calling_party = T2.calling_party and called_party = T2.called_party
)

select u1, u2, count(called_party) called_parties 
from (
select distinct u1, u2, called_party from 
(
        select a1.calling_party u1, a2.calling_party u2 from 
        (select calling_party from @T1 group by calling_party) a1,
        (select calling_party from @T1 group by calling_party) a2
) pairs,
 @T1 AS T
where
(u1 <> u2) and 
((u1 = t.calling_party and u2 <> t.called_party) or
(u2 = t.calling_party and u1 <> t.called_party))
) res
group by u1, u2
order by u1, u2