倒序--》从子节点查到顶级父节点
alter proc Up_Qry_Select
@CreditID int
as
with Credit1 as
(
select ct.CreditID,ct.parentCreditID from dbo.Credit ct
),
Credit2 as
(
select * from Credit1 where CreditID=@CreditID
union all
select Credit1.* from Credit2 inner join Credit1
on Credit2.parentCreditID=Credit1.CreditID
)
select * from Credit2
顺序---->你懂滴。。。
with CTE as
(
select a.CreditID,a.parentCreditID from Credit a where CreditID=4
union all
select cBase.CreditID,cBase.parentCreditID from Credit cBase inner join CTE c on cBase.parentCreditID=c.CreditID
)
select * from CTE