下面两种 

第一种：一般类型网上很多

代码：

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 

显示效果：

SQL无限级查询,With 递归_sql

第二种：树形显示

代码：

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

展示效果：

 

SQL无限级查询,With 递归_sql_02