sqlserver查询父子级关系

--自上往下搜索
;with maco as
(
select * from ty_Dictionary where id =21
union all 
select t.* from ty_Dictionary t,maco m where t.ParentID=m.ID
)
select distinct * from maco order by id desc

--自下往上搜索
;with maco as
(
select * from ty_Dictionary where id=23
union all 
select t.* from ty_Dictionary t,maco m where t.ID=m.ParentID
)
select * from maco order by id

;with maco as
(
select ID,ParentID from ty_Dictionary where ID=21
union all 
select t.ID,t.ParentID from ty_Dictionary t,maco m where t.ParentID=m.ID
)
delete from ty_Dictionary where ID in( select ID from maco group by ID)