如何创建存储过程以在用户之间的连接表中查找组

我意识到我的第一个答案只有在每个集团至少有一个用户没有被该集团中的任何其他人提及时才有效。也就是说,像A-B,B-C,C-A这样的封闭集团是找不到的。

与简单的案例相比,很难为每个集团找到一个独特的开端。

• 对邻居重新排序,以便对于所有引用A-B,A小于B,忽略任何A=B。

• 如果存在任何可能导致循环的X-A,请从中删除任何A-X引用。这永远不会完全删除对A的引用,因为X-A仍然存在,而A-X将添加到递归中。

-- 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

CREATE TABLE [dbo].[Users](
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [varchar](50) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Neighbours](
    [UserA] [int] NOT NULL,
    [UserB] [int] NOT NULL
) ON [PRIMARY]

UserA   UserB
1   2
2   3
4   5
4   6
5   7
7   8

然后:

WITH cliques(userid, clique) AS (
    SELECT  
        userid, ROW_NUMBER() OVER(ORDER BY userid) AS clique
    FROM
        Users
    WHERE
        users.UserID NOT IN (
            SELECT 
                UserB
            FROM
                Neighbours
        )
UNION ALL
    SELECT  
        Neighbours.UserB, clique 
    FROM
        neighbours 
        INNER JOIN cliques
            ON Neighbours.UserA = cliques.userid
)
SELECT
    clique, cliques.userid 
FROM 
    cliques
ORDER BY 
    clique, userid 

结果:

clique  userid
1   1
1   2
1   3
2   4
2   5
2   6
2   7
2   8

Recursive Queries Using Common Table Expressions

我添加了两个标签和两个GOTO语句

CREATE PROCEDURE [dbo].[Peamc_Test] 
AS 
BEGIN 

SET XACT_ABORT ON 
BEGIN TRAN 

SET NOCOUNT ON; 

CREATE TABLE #Users 
( 
UserId int NOT NULL, 
userLabel varchar(50) PRIMARY KEY NOT NULL, 
Observed bit NOT NULL 
) 

CREATE TABLE #Neighbors 
( 
UserId int NOT NULL, 
userLabel varchar(50) NOT NULL PRIMARY KEY, 
Retrieved bit NOT NULL 
) 

CREATE TABLE #ConnectedVertices 
( 
UserId int NOT NULL, 
userLabel varchar(50) NOT NULL PRIMARY KEY, 
) 

CREATE TABLE #Cliques 
( 
CliqueId int NOT NULL, 
UserId varchar(50) NOT NULL, 
) 

DECLARE @UsersCount int 
DECLARE @ii int 
DECLARE @User varchar(50) 
DECLARE @NeighborsCount int 

INSERT INTO #Users(UserId, userLabel, Observed) SELECT user_id, userLabel, 0 FROM dbo.test_users WHERE user_id IS NOT NULL 
SELECT @UsersCount = COUNT(*) FROM #Users 
SELECT @ii = 1 
WHILE @ii <= @UsersCount 
BEGIN 
--select user 
SELECT TOP 1 @User = userLabel FROM #Users WHERE Observed = 0 ORDER BY UserId 

UPDATE #Users SET Observed = 1 WHERE userLabel = @User 

--Get user's neighbors 
DELETE FROM #Neighbors 
INSERT INTO #Neighbors(UserId, userLabel, Retrieved) 
SELECT u.user_id, t2.neighbor, 0 FROM ( SELECT CALLING_NEIGHBORS.neighbor FROM ( SELECT mc.calling_party AS neighbor FROM monthly_connections_test mc WHERE mc.called_party = @User) AS CALLING_NEIGHBORS INNER JOIN (SELECT mc.called_party AS neighbor FROM monthly_connections_test mc WHERE mc.calling_party = @User) AS CALLED_NEIGHBORS ON CALLING_NEIGHBORS.neighbor = CALLED_NEIGHBORS.neighbor) AS t2 INNER JOIN test_users u ON t2.neighbor = u.userLabel 
SELECT @NeighborsCount = COUNT(*) FROM #Neighbors 
SELECT @ii = @ii + 1 
GOTO Clique_Find
--HERE the function recursive_find_cliques has to search for cliques and insert the found ones in #cliques 
--------------------
Clique_Return:
--------------------

END 

SELECT * FROM #Cliques 

END 

--------------------
Clique_Find:
--------------------
-- Code goes here
-- Code goes here
-- Code goes here
-- Code goes here
-- Code goes here
-- Code goes here
GOTO Clique_Return