Блог

Главная - Вопросы по программированию - T-SQL вычисляет иерархию / родителей

T-SQL вычисляет иерархию / родителей

#sql-server #tsql

#sql-сервер #tsql

Вопрос:

Мне нужно вычислить столбец «иерархия» на основе NR и NR_FROM-NR_TO, я не уверен, как создать CTE / VIEW с таким столбцом иерархии. Вот как должен выглядеть результат:

  ------ ---------------------- --------- ------- --------------------------- 
|  NR  |         Text         | NR_FROM | NR_TO |         HIERARCHY         |
 ------ ---------------------- --------- ------- --------------------------- 
| 1020 | AAAAAAAAAAAA         |         |       | /1380/1345/1300/1080/1051 |
| 1040 | BBBBBBB              |         |       | /1380/1345/1300/1080/1051 |
| 1045 | CCCCCCCCCCCCCCCCCCC  |         |       | /1380/1345/1300/1080/1051 |
| 1051 | DDDDDDDDDDD          |    1020 |  1045 | /1380/1345/1300/1080      |
| 1060 | EEEEEE               |         |       | /1380/1345/1300/1080      |
| 1080 | FFFF                 |    1051 |  1060 | /1380/1345/1300/1092      |
| 1090 | GGGGGGGGGGGGGGGG     |         |       | /1380/1345/1300/1092      |
| 1090 | So. betr. Erlöse     |         |       | /1380/1345/1300/1092      |
| 1092 | Betriebl. Rohertrag  |    1080 |  1090 | /1380/1345/1300           |
| 1100 | Abschreibungen       |         |       | /1380/1345/1300/1280      |
| 1100 | Abschreibungen       |         |       | /1380/1345/1300/1280      |
| 1110 | Personalk. Basis     |         |       | /1380/1345/1300/1280      |
| 1110 | Personalk. Basis     |         |       | /1380/1345/1300/1280      |
| 1120 | Personalk. Zusatz    |         |       | /1380/1345/1300/1280      |
| 1264 | Beratungsaufwand     |         |       | /1380/1345/1300/1280      |
| 1265 | sonstiger Aufwand    |         |       | /1380/1345/1300/1280      |
| 1280 | Gesamtkosten         |    1100 |  1265 | /1380/1345/1300           |
| 1300 | EBIT                 |    1000 |  1280 | /1380/1345                |
| 1310 | Zinsaufwand          |         |       | /1380/1345                |
| 1312 | Sonst. neutr. Aufw   |         |       | /1380/1345                |
| 1320 | Neutraler Aufwand    |    1310 |  1312 | /1380/1345                |
| 1322 | Zinserträge          |         |       | /1380/1345/1330           |
| 1323 | Sonst. neutr. Ertr   |         |       | /1380/1345/1330           |
| 1324 | Verr. kalk. Kosten   |         |       | /1380/1345/1330           |
| 1330 | Neutraler Ertrag     |    1322 |  1324 | /1380/1345                |
| 1345 | DSDSDSDSDSDD         |    1300 |  1320 | /1380                     |
| 1345 | DSDSDSDSDSDD         |    1330 |  1330 | /1380                     |
| 1355 | FDSFDSFSDFDSFSFSD    |         |       | /1380                     |
| 1380 | DDDAAA               |    1345 |  1355 | /                         |
 ------ ---------------------- --------- ------- ---------------------------

Комментарии:

1. Похоже, что нет корреляции между NR, NR_FROM, NR_TO и ИЕРАРХИЕЙ. Пожалуйста, отредактируйте вопрос, чтобы включить код SQL, который вы пробовали до сих пор, вместе с соответствующей схемой и образцами данных.

2. привязка — это номер, который не существует ни между одним из … у каждого члена есть все дочерние элементы с номером между от-до

3. SWAG : корень найден путем исключения всех строк с NR в любом из диапазонов от NR_FROM до NR_TO . Это входит в ваш якорь CTE. Оттуда рекурсивно добавляются строки на основе NR_FROM и NR_TO . Если бы вы предоставили полезные данные, например declare @Samples as Table ( ...); insert into @Samples ( ... ) values ...; , было бы намного проще предоставить рабочий пример.

Ответ №1:

Похоже, это решение дает вам то, что вам нужно.

Пример данных

 create table ranges
(
  nr int,
  txt nvarchar(20),
  nr_from int,
  nr_to int
);

insert into ranges (nr, txt, nr_from, nr_to) values
(1020, 'AAAAAAAAAAAA       ', null, null),
(1040, 'BBBBBBB            ', null, null),
(1045, 'CCCCCCCCCCCCCCCCCCC', null, null),
(1051, 'DDDDDDDDDDD        ', 1020, 1045),
(1060, 'EEEEEE             ', null, null),
(1080, 'FFFF               ', 1051, 1060),
(1090, 'GGGGGGGGGGGGGGGG   ', null, null),
(1090, 'So. betr. Erlöse   ', null, null),
(1092, 'Betriebl. Rohertrag', 1080, 1090),
(1100, 'Abschreibungen     ', null, null),
(1100, 'Abschreibungen     ', null, null),
(1110, 'Personalk. Basis   ', null, null),
(1110, 'Personalk. Basis   ', null, null),
(1120, 'Personalk. Zusatz  ', null, null),
(1264, 'Beratungsaufwand   ', null, null),
(1265, 'sonstiger Aufwand  ', null, null),
(1280, 'Gesamtkosten       ', 1100, 1265),
(1300, 'EBIT               ', 1000, 1280),
(1310, 'Zinsaufwand        ', null, null),
(1312, 'Sonst. neutr. Aufw ', null, null),
(1320, 'Neutraler Aufwand  ', 1310, 1312),
(1322, 'Zinserträge        ', null, null),
(1323, 'Sonst. neutr. Ertr ', null, null),
(1324, 'Verr. kalk. Kosten ', null, null),
(1330, 'Neutraler Ertrag   ', 1322, 1324),
(1345, 'DSDSDSDSDSDD       ', 1300, 1320),
(1345, 'DSDSDSDSDSDD       ', 1330, 1330),
(1355, 'FDSFDSFSDFDSFSFSD  ', null, null),
(1380, 'DDDAAA             ', 1345, 1355);

Решение

 with cte as
(
  select r.nr, r.txt, r.nr_from, r.nr_to, 1 as lvl, convert(nvarchar(100), '') as hierarchy
  from ranges r
  where not exists ( select 'x'
                     from ranges r2
                     where r2.nr_from <= r.nr
                       and r2.nr_to >= r.nr)
    union all
  select r.nr, r.txt, r.nr_from, r.nr_to, lvl 1, convert(nvarchar(100), cte.hierarchy   '/'   convert(nvarchar(4), cte.nr))
  from ranges r
  join cte
    on  cte.nr_from <= r.nr
    and cte.nr_to >= r.nr
)
select cte.nr,
       cte.txt,
       cte.nr_from,
       cte.nr_to,
       case when cte.lvl = 1 then '/' else cte.hierarchy end as hierarchy
from cte
where not exists ( select 'x'
                   from cte c2
                   where c2.nr = cte.nr
                     and c2.lvl > cte.lvl )
order by cte.nr;

Скрипка

Комментарии:

1. Ваши результаты начинают иерархию с /1345 , а не /1380 как в ожидаемых результатах OP.

2. @HABO, хороший улов. Должно быть исправлено сейчас. Все еще не на 100% совпадает с результатом OP, но я не думаю, что это на моей стороне сейчас. Nr 1020 создает иерархию /1380/1345/1300/1092/1080/1051 . Я думаю, что OP забыл 1092 в своем ожидаемом результате ( 1092 является родительским для [ 1080 , 1090 ]).

3. Эй, выглядит великолепно (еще не пробовал). Да, моя ошибка, не хватает 1092. Спасибо