sql - SQL Server 函数中的递归游标不起作用

Question

这是表结构。

select nid, memberid, sponsorid 
from tblmember;

这会产生以下结果:

nid memerid sponsor
-------------------
1   679414  0
2   622411  679414
3   647964  679414
5   285631  679414
6   119979  

我正在尝试创建一个类似树的结构，其中每个节点只能有三个子节点，因此如果具有 memberid 679414 的成员引入一个新分支并且他已经有三个子节点，那么新分支如果 679414 的子节点少于 3 个，则将其添加到第一个子节点。 Sponsorid是父节点的Memberid(唯一)。为了查找 679414 树中子节点少于 3 个的第一个节点，我使用光标创建了以下函数。

ALTER FUNCTION dbo.getSponsor (@id VARCHAR(50))
RETURNS VARCHAR(101)
AS
BEGIN
    DECLARE @cou INT;
    DECLARE @id1 VARCHAR(200);

    SELECT @cou = count(*)
    FROM tblmember
    WHERE sponsorid = @id

    IF (@cou < 3)
    BEGIN
        RETURN @id
    END
    ELSE
    BEGIN
        DECLARE db_cursor LOCAL FOR
            SELECT memberid
            FROM dbo.tblmember
            WHERE sponsorid = @id

        OPEN db_cursor

        FETCH NEXT FROM db_cursor INTO @id1

        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC getsponsor @id1;
        END

        CLOSE db_cursor
        DEALLOCATE db_cursor
    END

    RETURN '';
END

但是当我使用以下代码尝试时，这个光标没有被执行。

DECLARE @a VARCHAR(200);
EXEC @a = [getSponsor] '679414'
SELECT @a;

我是不是做错了什么？我在网上查了一下，发现游标是全局的，因此在递归中它会导致问题，所以我将 Local 放在游标定义中。

Answer 1

我们可以使用递归公用表表达式来解决这个问题。计算完节点树后，我们忽略使用的节点。

;WITH CTE AS (
    SELECT *, 1 R FROM tblmember T WHERE sponsorid = @id
    UNION ALL 
    SELECT T.id, T.memberid, T.sponsorid, R + 1 R FROM tblmember T INNER JOIN CTE ON T.sponsorid = CTE.memberid
)
,UsedNodes AS (
    SELECT sponsorid, R FROM CTE 
    GROUP BY sponsorid, R
    HAVING COUNT(*) = 3
)
SELECT TOP 1 @return_id = memberid 
FROM CTE 
WHERE memberid NOT IN ( SELECT sponsorid FROM UsedNodes )
ORDER BY R, id