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

@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

Новые блоги и статьи Все статьи Все блоги /
SDL3 для Web (WebAssembly): Обработчик клика мыши в браузере ПК и касания экрана в браузере на мобильном устройстве 8Observer8 02.02.2026 Содержание блога Для начала пошагово создадим рабочий пример для подготовки к экспериментам в браузере ПК и в браузере мобильного устройства. Потом напишем обработчик клика мыши и обработчик. . .	Философия технологии iceja 01.02.2026 На мой взгляд у человека в технических проектах остается роль генерального директора. Все остальное нейронки делают уже лучше человека. Они не могут нести предпринимательские риски, не могут. . .	SDL3 для Web (WebAssembly): Вывод текста со шрифтом TTF с помощью SDL3_ttf 8Observer8 01.02.2026 Содержание блога В этой пошаговой инструкции создадим с нуля веб-приложение, которое выводит текст в окне браузера. Запустим на Android на локальном сервере. Загрузим Release на бесплатный. . .	SDL3 для Web (WebAssembly): Сборка C/C++ проекта из консоли 8Observer8 30.01.2026 Содержание блога Если вы откроете примеры для начинающих на официальном репозитории SDL3 в папке: examples, то вы увидите, что все примеры используют следующие четыре обязательные функции, а. . .
SDL3 для Web (WebAssembly): Установка Emscripten SDK (emsdk) и CMake для сборки C и C++ приложений в Wasm 8Observer8 30.01.2026 Содержание блога Для того чтобы скачать Emscripten SDK (emsdk) необходимо сначало скачать и уставить Git: Install for Windows. Следуйте стандартной процедуре установки Git через установщик. . . .	SDL3 для Android: Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 29.01.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами. Версия v3 была полностью переписана на Си, в. . .	Инструменты COM: Сохранение данный из VARIANT в файл и загрузка из файла в VARIANT bedvit 28.01.2026 Сохранение базовых типов COM и массивов (одномерных или двухмерных) любой вложенности (деревья) в файл, с возможностью выбора алгоритмов сжатия и шифрования. Часть библиотеки BedvitCOM Использованы. . .	SDL3 для Android: Загрузка PNG с альфа-каналом с помощью SDL_LoadPNG (без SDL3_image) 8Observer8 28.01.2026 Содержание блога SDL3 имеет собственные средства для загрузки и отображения PNG-файлов с альфа-каналом и базовой работы с ними. В этой инструкции используется функция SDL_LoadPNG(), которая. . .

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

Решение