sql循环(WITH AS短语也叫做子查询部分)
--表结构 SELECT id,position,Parentid FROM op_client_sales_structure
WITH TEST_CTE AS ( SELECT id,position,Parentid,Cast(Parentid AS NVARCHAR(4000)) AS PATH FROM op_client_sales_structure team WHERE Parentid !=-1 UNION ALL SELECT a.id,a.position,a.Parentid, CTE.PATH+','+Cast(a.Parentid AS NVARCHAR(4000)) AS PATH FROM op_client_sales_structure a INNER JOIN TEST_CTE CTE ON a.id=CTE.Parentid ) SELECT * FROM TEST_CTE WHERE Parentid=(SELECT id FROM op_client_sales_structure WHERE Parentid=-1) --限制递归次数 OPTION(MAXRECURSION 10)