--cteChildren 子孙信息
--cteParents 父亲信息
with cteChildren(EmpID,ParentID)
AS
(
select EmpID,ParentID from Persons where parentid = 10171
union all
select t.EmpID,t.parentid from Persons as t
inner join cteChildren as c on t.ParentID = c.Id
),
cteParents(EmpID,ParentID)
AS
(
select EmpID,ParentID from Persons where Id= 10172
union all
select t.EmpID,t.parentid from Persons as t
inner join cteParents as c on t.Id= c.ParentID
)
--合并查询
select Idfrom cteChildren
UNION
select Idfrom cteParents
SqlServer_合并多个递归查询数据（CTE）
原文：http://www.cnblogs.com/gossip/p/4576306.html
