SQL Server 递归查询以显示父路径 [英] SQL Server recursive query to show path of parents
本文介绍了SQL Server 递归查询以显示父路径的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用 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屋!
查看全文