;WITH CTE AS( SELECT *,CAST(B AS VARCHAR(8000))+','+CAST(A AS VARCHAR(10)) [PATH] FROM TABLENAME WHERE B=-1 UNION ALL SELECT T1.*,T2.[PATH]+','+CAST(T1.A AS VARCHAR(10)) FROM TABLENAME T1 INNER JOIN CTE T2 ON T1.B=T2.A ) SELECT T1.A,T1.B FROM CTE T1 JOIN CTE T2 ON T1.[PATH]+','LIKE T2.[PATH]+',%' WHERE T2.B=-1 你参考一下
理解错了。可以试一下下面的sqlwith cte as ( select * from tablename where B=-1 union all select a.* from tablename as a, cte as b where a.B=b.A ) select * from cte
with cte as (select * from tablename where b=-1 unino all select * from tablename as a join cte as b on a.b=b.a ) select * from cte
WITH A (id,num) AS ( SELECT 1,-1 UNION all SELECT 2,1 UNION all SELECT 3,2 UNION all SELECT 4,3 UNION all SELECT 5,4 ),cte AS ( SELECT * FROM A WHERE num=-1 UNION ALL SELECT A.* FROM cte AS c , A where c.id=A.num ) SELECT * FROM cte
SELECT *,CAST(B AS VARCHAR(8000))+','+CAST(A AS VARCHAR(10)) [PATH] FROM TABLENAME WHERE B=-1
UNION ALL
SELECT T1.*,T2.[PATH]+','+CAST(T1.A AS VARCHAR(10)) FROM TABLENAME T1 INNER JOIN CTE T2 ON T1.B=T2.A
)
SELECT T1.A,T1.B FROM CTE T1 JOIN CTE T2 ON T1.[PATH]+','LIKE T2.[PATH]+',%' WHERE T2.B=-1
你参考一下
(
select * from tablename where B=-1
union all
select a.* from tablename as a, cte as b where a.B=b.A
)
select * from cte
(select * from tablename where b=-1
unino all
select * from tablename as a join cte as b
on a.b=b.a )
select * from cte
SELECT 1,-1 UNION all
SELECT 2,1 UNION all
SELECT 3,2 UNION all
SELECT 4,3 UNION all
SELECT 5,4
),cte AS (
SELECT * FROM A WHERE num=-1 UNION ALL
SELECT A.* FROM cte AS c , A where c.id=A.num
)
SELECT * FROM cte