SQL CTE 记录
时间：2021-07-01 10:21:17
帮助过：2人阅读
ID , UserName , Vcode , Fromcode ,
0 AS [count]
INTO #heihei
FROM dbo.RegInfo;
DECLARE @p_id INT;
DECLARE @p_UserName VARCHAR(
20);
DECLARE @p_Vcode VARCHAR(
20);
DECLARE @p_Fromcode VARCHAR(
20);
DECLARE @count VARCHAR(
20);
DECLARE auth_cur
CURSOR
FOR
SELECT *
FROM #heihei;
OPEN auth_cur;
FETCH NEXT FROM auth_cur
INTO @p_id,
@p_UserName,
@p_Vcode,
@p_Fromcode,
@count;
WHILE (
@@fetch_status = 0 )
BEGIN
DECLARE @vvid INT = @p_Vcode;
DECLARE @nowcount VARCHAR(
20);
WITH cteTree
AS (
SELECT ID , Vcode , Fromcode
FROM #heihei
WHERE Vcode
= @vvid
UNION ALL
SELECT b.ID , b.Vcode , b.Fromcode
FROM cteTree
INNER JOIN #heihei b
ON cteTree.Vcode
= b.Fromcode
)
SELECT @nowcount = COUNT(id)
FROM cteTree
WHERE Vcode
!= @vvid
--OPTION (Maxrecursion 10) ;
UPDATE #heihei
SET [count] = @nowcount
WHERE ID
= @p_id;
FETCH NEXT FROM auth_cur
INTO @p_id,
@p_UserName,
@p_Vcode,
@p_Fromcode,
@count;
END;
CLOSE auth_cur;
DEALLOCATE auth_cur;
SELECT *
FROM #heihei;
DROP TABLE #heihei;
查询注册邀请好友的数量
SQL CTE 记录
标签：
