Выборка с условием по иерархической таблице

@kotelok · Регистрация: 08.08.2014

Студворк — интернет-сервис помощи студентам

Есть две таблицы:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
create table item
(
    item_id int not null primary key,
    parent_item_id int null -- ссылается на саму себя, т.е. на 'item.item_id'
);
 
create table sub_item
(
    sub_item_id int identity (1, 1) not null primary key,
    item_id int not null, -- FK
 
    constraint fk_item_sub_item foreign key (item_id) references item (item_id)
);

И немного тестовых данных:

Кликните здесь для просмотра всего текста

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
insert into item values (1, null);
insert into item values (2, null);
insert into item values (3, 1);
insert into item values (4, 2);
insert into item values (5, 3);
insert into item values (6, 4);
insert into item values (7, null);
insert into item values (8, null);
insert into item values (9, 8);
insert into item values (10, 9);
 
insert into sub_item (item_id) values (1);
insert into sub_item (item_id) values (6);

При помощи такого запроса можно получить все записи из таблицы 'item', у которых есть хотя бы одна detail-запись в таблице 'sub_item' (в данном случае это будут 'item_id = 1' и 'item_id = 6'):

T-SQL
1
select * from item where exists (select 1 from sub_item where sub_item.item_id = item.item_id);

А как получить все 'select * from item'-записи по условию, что есть хотя бы одна detail-запись не только у самой записи, но и хотя бы у одной parent-записи на всей цепочке иерархии, вплоть до корневой записи (parent_item_id is null), т.е. при указанных тестовых данных этот будут записи 1, 3, 5 и 6.

@PaulWist · 15.10.2024, 15:11

Сообщение от kotelok

т.е. при указанных тестовых данных этот будут записи 1, 3, 5 и 6.

Не понятно, почему выпали записи item_id = 4 и 2, которые являются веткой "в верх" потомка 6.

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
with cte as 
(
select item_id, parent_item_id  from item where exists (select 1 from sub_item where sub_item.item_id = item.item_id)
union all
select item.item_id, item.parent_item_id from cte
inner join item on item.parent_item_id = cte.item_id
)
, 
cte2 as 
(
select item_id, parent_item_id  from item where exists (select 1 from sub_item where sub_item.item_id = item.item_id)
 
union all
select item.item_id, item.parent_item_id from cte2
inner join item on cte2.parent_item_id = item.item_id
)
 
select * from cte2
union 
select * from cte order by parent_item_id, item_id

@kotelok · 15.10.2024, 15:17 **[ТС]**

Сообщение от PaulWist

Не понятно, почему выпали записи item_id = 4 и 2

Потому что я, видимо, не совсем понятно задачу сформировал.

Запись должна попадать в выборку при условии что хотя бы одна detail-запись есть:
1. Либо у неё самой.
2. Либо у одного из её предков (на любую глубину).

Наличие detail-записей у потомков этой записи на выборке никак сказываться не должно.

@Аватар · 15.10.2024, 15:17

SQL
1
2
3
4
5
6
7
8
9
10
WITH cte AS
(SELECT i.*,CASE WHEN s.item_id IS NOT NULL THEN 1 ELSE 0 END priz 
   FROM item i 
     LEFT JOIN sub_item s ON s.item_id=i.item_id 
   WHERE i.parent_item_id IS NULL
 UNION ALL
 SELECT i.*,CASE WHEN cte.priz=1 OR EXISTS(SELECT 8 FROM sub_item s WHERE s.item_id=i.item_id) THEN 1 ELSE 0 END priz
   FROM item i
     INNER JOIN cte ON cte.item_id=i.parent_item_id)
SELECT item_id,parent_item_id FROM cte WHERE priz=1 ORDER BY item_id

Новые блоги и статьи Все статьи Все блоги /
Отчёт о затраченных материалах за определенный период с макетом печатной формы Maks 21.04.2026 Отчёт из решения ниже размещён в конфигурации КА2. Задача: разработка отчёта по затраченным материалам за определённый период, с возможностью вывода печатной формы отчёта с шапкой и подвалом. В. . .	Отчёт о спецтехнике находящейся в ремонте Maks 20.04.2026 Отчёт из решения ниже размещен в конфигурации КА2. Задача: отобразить спецтехнику, которая на данный момент находится в ремонте. Есть нетиповой документ "Заявка на ремонт спецтехники" который. . .	Памятка для бота и "визитка" для читателей "Semantic Universe Layer (Слой семантической вселенной)" Hrethgir 19.04.2026 Сгенерировано для краткого описания по случаю сборки и компиляции скелета серверного приложения. И пусть после этого скажут, что статьи сгенерированные AI - туфта и не интересно. И это не реклама -. . .	Запрет удаления строк ТЧ документа при определённом условии Maks 19.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "Аккумуляторы", разработанного в конфигурации КА2. У данного документа есть ТЧ, в которой в зависимости от прав доступа. . .
Модель заражения группы наркоманов alhaos 17.04.2026 Условия задачи сформулированы тут Суть: - Группа наркоманов из 10 человек. - Только один инфицирован ВИЧ. - Колются одной иглой. - Колются раз в день. - Колются последовательно через. . .	Мысли в слух. Про "навсегда". kumehtar 16.04.2026 Подумалось тут, что наверное очень глупо использовать во всяких своих установках понятие "навсегда". Это очень сильное понятие, и я только начинаю понимать край его смысла, не смотря на то что давно. . .	My Business CRM MaGz GoLd 16.04.2026 Всем привет, недавно возникла потребность создать CRM, для личных нужд. Собственно программа предоставляет из себя базу данных клиентов, в которой можно фиксировать звонки, стадии сделки, а также. . .	Знаешь почему 90% людей редко бывают счастливыми? kumehtar 14.04.2026 Потому что они ждут. Ждут выходных, ждут отпуска, ждут удачного момента. . . а удачный момент так и не приходит.

Опции темы

Выборка с условием по иерархической таблице

Решение