我正在处理SQL Server语句,有一个类似的表:
| item | value | parentItem |
+------+-------+------------+
| 1 | 2test | 2 |
| 2 | 3test | 3 |
| 3 | 4test | 4 |
| 5 | 1test | 1 |
| 6 | 3test | 3 |
| 7 | 2test | 2 |
我希望使用SQL Server语句获得以下结果:
| 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的值。
我尝试过这个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"列如下所示:
/4test/4test/4test/3test/3test/3test/3test/2test/2test/2test/2test
你能帮我翻译一下吗?谢谢。
MERGE
,你尝试过什么? - undefined