在网上找了个SQL无限级查询,以树形显示的查询 ,没找到,自己写了一个,想到也有别的兄弟也可能遇到,拿出看看希望有所帮助
下面两种
第一种:一般类型网上很多
代码:
with T as
(
select * from va where PID =0
union all
select a.* from [va] a join T b on a.[PID] = b.CID
)
select * from T
显示效果:
第二种:树形显示
代码:
WITH T AS
(
SELECT *,CAST(CID AS VARBINARY(MAX)) AS f
FROM va AS A
WHERE NOT EXISTS(SELECT * FROM va WHERE CID=A.[PID])
UNION ALL
SELECT A.*,CAST(B.f+CAST(A.CID AS VARBINARY) AS VARBINARY(MAX))
FROM va AS A
JOIN T AS B
ON A.[PID]=B.CID
)
SELECT [CID],[PID],[Name] FROM T
ORDER BY f
展示效果: