表结构
查id为12上面7层
WITH TEST_CTE
AS
(
SELECT TBM.[qrcode_userid],TBM.[phone],TBM.[invite_code],TBM.[parent_id],Cast(TBM.[qrcode_userid] as nvarchar(4000)) AS PATH FROM [FinancialPayDB].[dbo].[fp_qrcode_user] TBM
WHERE [qrcode_userid] = 12
UNION ALL
SELECT CTBM.[qrcode_userid],CTBM.[phone],CTBM.[invite_code],CTBM.[parent_id],CTE.PATH+'->'+Cast(CTBM.[qrcode_userid] as nvarchar(4000)) PATH FROM [FinancialPayDB].[dbo].[fp_qrcode_user] CTBM
INNER JOIN TEST_CTE CTE ON CTBM.[qrcode_userid]=CTE.[parent_id]
)
SELECT * FROM TEST_CTE
--限制递归次数
OPTION(MAXRECURSION 7);
同理,如果要查下面7层,把CTBM.[qrcode_userid]=CTE.[parent_id] 换过来 CTBM.[parent_id]=CTE.[qrcode_userid] 就变成查下面7层。