Рекурсивный запрос SQL Server для отображения пути к родителям

#sql #sql-server #sql-server-2008-r2

#sql #sql-сервер #sql-server-2008-r2

Вопрос:

Я работаю с инструкциями 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.

Я пробовал этот 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

Не могли бы вы, пожалуйста, помочь мне с этим? Большое спасибо.

1. Вы просто хотите это в качестве выходных данных? Или вы хотите изменить данные в таблице на другую структуру? Я не думаю, что вам нужно MERGE в любом случае, что вы пробовали?

2. Спасибо за вопросы. Я обновил вопросы. Не могли бы вы, пожалуйста, помочь проверить это?

3. SQL Server 2008 R2

4. Хорошо, особенно при использовании устаревшей и больше не поддерживаемой версии, всегда полезно пометить ее заранее, потому что люди, как правило, ожидают более новые версии (где решение может быть другим / лучше).

5. Привет @AnneLiu, отличная работа!. Мое решение, приведенное ниже, очень похоже. Я думаю, вам не хватает GROUP BY при окончательном выборе

Ответ №1:

основываясь на полученном вами ожидаемом результате, используйте рекурсивную часть для объединения значения

 ;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

Ответ №2:

Вот пример данных

 drop table if exists dbo.test_table;
go
create table dbo.test_table(
  item                  int not null,
  [value]               varchar(100) not null,
  parentItem            int not null);

insert dbo.test_table values
(1,'test1',2),
(2,'test2',3),
(3,'test3',4),
(5,'test4',1),
(6,'test5',3),
(7,'test6',2);

Вот запрос

 ;with recur_cte(item, [value], parentItem, h_level) as (
    select item, [value], parentItem, 1 
    from dbo.test_table tt
    union all
    select rc.item, tt.[value], tt.parentItem, rc.h_level 1 
    from dbo.test_table tt join recur_cte rc on tt.item=rc.parentItem)
select rc.item, 
      stuff((select '/'   cast(parentItem as varchar)
             from recur_cte c2
             where rc.item = c2.item
             order by h_level desc FOR XML PATH('')), 1, 1, '') [value1]
from recur_cte rc
group by item;

Вот результаты

 item    value1
1       4/3/2
2       4/3
3       4
5       4/3/2/1
6       4/3
7       4/3/2