Блог

Главная - Вопросы по программированию - Получение составных деталей компонентов с помощью рекурсивного cte SQL

Получение составных деталей компонентов с помощью рекурсивного cte SQL

#sql #sql-server #recursion #common-table-expression

Вопрос:

Добрый день! Там есть таблица, в которой хранятся компоненты автомобиля. Автомобиль состоит из составных частей, и каждая деталь может состоять из своей фирмы. На выходе я хочу получить список всех использованных деталей. Для этого я хочу использовать рекурсивный CTE. Уровень несения заранее не известен. Но я не могу понять, как это реализовать.

Например:

  --------- -------------- ----- -------------- 
| main ID |     Name     | cID |  Component   |
 --------- -------------- ----- -------------- 
|       1 | Car          |  11 | Engine       |
|       1 | Car          |  12 | Transmission |
|       1 | Car          |  13 | Body         |
|      11 | Engine       |  21 | Crankshaft   |
|      11 | Engine       |  22 | Piston       |
|      11 | Engine       |  23 | Valve        |
|      12 | Transmission |  31 | Carter       |
|      12 | Transmission |  32 | Differential |
|      12 | Transmission |  33 | Lead shaft   |
 --------- -------------- ----- --------------

Далее я хочу получить список:

  --------------- 
|  Components   |
 --------------- 
| Engine        |
| Crankshaft    |
| Piston        |
| Valve         |
| Transmission  |
| Carter        |
| Differential  |
| Lead shaft    |
| Body          |
 ---------------

Пожалуйста, не судите строго — это мой первый опыт использования рекурсивного CTE

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

1. Какие СУБД вы используете?

2. @jarlh, MS SQL SERVER

3. Я бы начал с нормализации дизайна вашей базы данных. У вас там избыточность, которая может привести к беспорядку.

4. @Цепеш, К сожалению, структура таблицы не может быть изменена, потому что она создана сторонними производителями программного обеспечения и работает с этим программным обеспечением. Приходится работать с тем, что есть

Ответ №1:

Вы можете ходить по графику на нескольких уровнях, используя рекурсивный CTE, как вы упомянули.

Например:

 with
n as (
  select component, '/' || cid as path 
  from t 
  where main_id not in (select cid from t)
 union all
  select t.component, n.path || cid from n join t on t.main_id = n.cid
)
select component
from n
order by path

Это всего лишь пример. Вы можете добавить дополнительные столбцы по мере необходимости и настроить начальные узлы (изменив элемент привязки) или путь обхода (изменив рекурсивный элемент).

Метки: