我意识到，只有当每个集团都有至少一个该集团中其他任何人都没有提及的用户时，我的第一个答案才有效.换句话说，将找不到诸如A-B，B-C，C-A之类的封闭集团.

I realised that my first answer only works when each clique has at least one user who is not referred to by any others in that clique. In other words, closed cliques like A-B, B-C, C-A will not be found.

这是解决此问题的解决方案.同样，我们有ID为1..20的用户.邻居关系有几种情况需要处理:

Here is a solution which solves this. Again we have users with IDs, now 1..20. There are several cases of neighbouring relations that need to be handled:

与简单情况相比，很难为每个集团找到一个独特的启动器.我们只需一点技巧即可实现:

Compared to the simple case, it is harder to find a unique starter for each clique. We achieve this with a little sleight of hand:

• 对邻居进行重新排序，以便对于所有引用A-B，A小于B，而忽略任何A = B.

• Reorder the neighbours so that for all references A-B, A is less than B, ignoring any A=B.

如果有可能导致循环的X-A，请从其中删除所有A-X引用.这将永远不会完全删除对A的引用，因为保留了X-A并将A-X添加到递归中.

From these, remove any A-X references if there are any X-A, which could cause a loop. This will never remove references to A completely because X-A remains and A-X will be added in the recursion.

结果集是开始"用户，我们使用它们来填充CTE:

The resultant set are the 'starting' users and we use them to prime the CTE:

-- Get all pairs, where UserA < UserB, dropping any A=B or B=A
WITH LRNeighbours(A, B) AS (
    SELECT
        Neighbours.UserA, Neighbours.UserB
    FROM
        Neighbours
    WHERE
        Neighbours.UserA < Neighbours.UserB
UNION ALL
    SELECT DISTINCT
        Neighbours.UserB, Neighbours.UserA
    FROM
        Neighbours
    WHERE
        Neighbours.UserA > Neighbours.UserB
),
-- Isolate those that are not referred to by a higher numbered key
Starters(userid) AS (
    SELECT DISTINCT
        A
    FROM    
        LRNeighbours
    WHERE 
        A NOT IN (
            SELECT 
                B
            FROM
                LRNeighbours
        )
),
-- The recursive Common Table Expression
cliques(userid, clique) AS (
    -- Number starters 1..N
    SELECT  
        userid, ROW_NUMBER() OVER(ORDER BY userid) AS clique
    FROM
        Starters
UNION ALL
    -- Recurse, adding users referred by siblings, avoiding starters themselves
    SELECT  
        B, clique 
    FROM
        LRNeighbours INNER JOIN 
        cliques ON
            LRNeighbours.A = cliques.userid 
            AND B NOT IN (
                SELECT
                    userid
                FROM
                    starters
            )
)
SELECT DISTINCT
    clique, userid 
FROM 
    cliques 
ORDER BY 
    clique, userid 

结果:

1   1
1   2
2   3
2   4
3   5
3   6
3   7
3   8
4   9
4   10
4   11
4   12
4   13
5   14
5   15
5   16
5   17
5   18
5   19
5   20