SQL Server 递归查询以显示父路径 [英] SQL Server recursive query to show path of parents

问题描述

我正在使用 SQL Server 语句并且有一个表，例如:

I am working with SQL Server statements and have one table like:

| item | value | parentItem |
+------+-------+------------+
|  1   | 2test |     2      |
|  2   | 3test |     3      |
|  3   | 4test |     4      |
|  5   | 1test |     1      |
|  6   | 3test |     3      |
|  7   | 2test |     2      |

我想使用 SQL Server 语句获得以下结果:

And I would like to get the below result using a SQL Server statement:

| item1 | value1                   |
+-------+--------------------------+
|  1    | /4test/3test/2test       |
|  2    | /4test/3test             |
|  3    | /4test                   |
|  5    | /4test/3test/2test/1test |
|  6    | /4test/3test             |
|  7    | /4test/3test/2test       |

我没有找出正确的 SQL 来根据 parentItem 获取所有 id 的所有值.

I didn't figure out the correct SQL to get all the values for all the ids according to parentItem.

我试过这个 SQL :

I have tried this SQL :

with all_path as 
(
    select item, value, parentItem 
    from table 

    union all 

    select a.item, a.value, a.parentItem 
    from table a, all_path b
    where a.item = b.parentItem
)
select 
    item as item1, 
    stuff(select '/' + value 
          from all_path 
          order by item asc 
          for xml path ('')), 1, 0, '') as value1 
from 
    all_path

但是得到了value1"结果列如

But got the "value1" column in result like

/4test/4test/4test/3test/3test/3test/3test/2test/2test/2test/2test

你能帮我解决这个问题吗?非常感谢.

Could you please help me with that? Thanks a lot.

推荐答案

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

based on the expected output you gave, use the recursive part to concatenate the value

;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

这篇关于SQL Server 递归查询以显示父路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！