sql with递归

首先表结果如下

select MID,MPID from Member_Tbl

比如要找到MID=1的所有子级元素，子级元素在找子子级元素...，直至null,也就是向下递归

declare @MID int
set @MID = 1
; with cte as
(
select MID,MPID from Member_Tbl where MID = @MID
union all
select m.MID,m.MPID from cte inner join Member_TBl m on cte.MID = m.MPID
)
select * from cte

结果：

如果要从子级元素开始找起，比如MID=3,MPID=2，要找它的父级元素也就是MID=2,然后在找MID=2的父级元素，

直至null

declare @MID int
set @MID = 3
; with cte as
(
select MID,MPID from Member_TBl where MID = @MID
union all
select m.MID,m.MPID from cte inner join Member_Tbl m on cte.MPID = m.MID
)
select * from cte

结果：

转载于:https://www.cnblogs.com/linlin/archive/2011/04/20/2021863.html