Sql server中递归的用法

现有如下表

1.由子类查询父类

    with sonTofather as
(
  select cid,NAME,Parent_Cid from [Table] where Cid=50023021
  union all
  select a.Cid,a.Name,a.Parent_Cid from [Table] a join sonTofather b on a.Cid=b.Parent_Cid where b.Parent_Cid is not null  
)


select * from sonTofather

结果

2.由父类查子类

WITH lmenu(cid,name,Parent_Cid,level) as
 (
    SELECT cid,NAME,Parent_Cid,0 level FROM [Table] WHERE Cid=50010099
    UNION ALL
    SELECT A.Cid,A.NAME,A.Parent_Cid,B.level+1 FROM [Table] A,lmenu B
    where A.Parent_Cid = B.Cid
)
SELECT *  from lmenu

结果：