利用SQLServer递归查询

 

倒序--》从子节点查到顶级父节点

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