sqlserver 递归并且按需中断

参考：https://www.cnblogs.com/sheng-jie/p/6347835.html

          https://blog.csdn.net/maxiaozhi8/article/details/93059238

 

 

解释一下：

--usb_recommend_id    父ID

--usb_us_id   用户ID

@mylevel mylevel  递归100次退出，只运行到第100层级，如果不加，OPTION递归必须要大于等于递归最大层级

OPTION 递归层级，默认100

 

declare @mylevel int;
CREATE TABLE #temp
(
usb_recommend_id NVARCHAR(20),
usb_us_id NVARCHAR(20),
path1 NVARCHAR(MAX)
);
WITH TEST_CTE 
AS
(
SELECT TBIE.usb_recommend_id,TBIE.usb_us_id,1 as mylevel,CAST(TBIE.usb_us_id as nvarchar(4000)) AS PATH1
FROM us_userInfo TBIE
WHERE TBIE.usb_us_id = 30
UNION ALL
SELECT CTBIE.usb_recommend_id,CTBIE.usb_us_id,CTE.mylevel+1,CTE.PATH1+'>'+Cast(CTBIE.usb_us_id as nvarchar(4000)) PATH1 
FROM us_userInfo CTBIE
INNER JOIN TEST_CTE CTE ON CTBIE.usb_recommend_id=CTE.usb_us_id and CTE.mylevel < 100
)
--select * into #table_Name from TEST_CTE

insert into #temp (usb_recommend_id,usb_us_id,path1) select usb_recommend_id,usb_us_id,path1 FROM TEST_CTE OPTION(MAXRECURSION 100)
--if @mylevel < 10 BEGIN
-- return @mylevel
-- END
-- RETURN NULL
--SELECT * FROM #temp 
SELECT TOP 1 usb_us_id, len(path1) - len(replace(path1,'>','')) c_count,
--SUBSTRING(path1,charindex('>',path1)+1,len(path1)) 
SUBSTRING(
substring(path1,charindex('>',path1)+1,len(path1)-charindex('>',path1)),0,CHARINDEX('>',substring(path1,charindex('>',path1)+1,len(path1)-charindex('>',path1)))) AS mychild
FROM #temp ORDER BY c_count DESC

DELETE FROM #temp
DROP TABLE #temp