SQL Server 递归查询以显示父路径

MLe*_*anc 5

根据您给出的预期输出，使用递归部分连接值

;with yourTable as (
     select item, value, parentItem 
     from (values 
     (1,'2test',2)
    ,(2,'3test',3)
    ,(3,'4test',4)
    ,(5,'1test',1)
    ,(6,'3test',3)
    ,(7,'2test',2)
    )x (item,value,parentItem)
)
, DoRecursivePart as (

    select 1 as Pos, item, convert(varchar(max),value) value, parentItem 
    from yourTable
    union all
    select drp.pos +1, drp.item, convert(varchar(max), yt.value + '/' + drp.value), yt.parentItem
    from yourTable yt
    inner join DoRecursivePart drp on drp.parentItem = yt.item

)
select drp.item, '/' + drp.value 
from DoRecursivePart drp
inner join (select item, max(pos) mpos 
            from DoRecursivePart 
            group by item) [filter] on [filter].item = drp.item and [filter].mpos = drp.Pos
order by item

给

item        value
----------- ------------------
1           /4test/3test/2test
2           /4test/3test
3           /4test
5           /4test/3test/2test/1test
6           /4test/3test
7           /4test/3test/2test