sqlserver查询父子级关系

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)

 

sqlserver查询父子级关系

标签：order   desc   ict   dict   group by   parent   serve   style   mac