Как выбрать рекурсивно в mysql?
у меня две таблицы
components
id | number | name | context | ...
-------------------------------------
1 | 1234 | Test | car | ...
2 | 1235 | Test | car | ...
3 | 1236 | Test | car | ...
4. | 1237 | Test | car | ...
... | ... | ... | car | ...
_____________________________________
> 100.000 rows
boms
id | parent | child | count
----------------------------
1 | 1234 | 1235 | 1
2 | 1234 | 1236 | 1
3 | 1236 | 1237 | 2
... | ... | ... | ...
____________________________
> 500.000 rows
Цель этой таблицы в том, что components — это список компонентов со всеми подробностями, относящимися к этому компоненту. Таблица boms представляет собой «Список материалов», в котором показано, какой компонент встроен в другой компонент и сколько раз.
Результат в виде дерева:
1234
|-- 1x 1235
|-- 1x 1236
|-- 2x 1237
Есть много компонентов и много бомб, а также много контекстов. Сколько их неизвестно и сколько уровней может иметь спецификация, также неизвестно.
Поскольку я вообще не знаю, как сделать следующее, к сожалению, я не могу предоставить фрагмент кода:
TLTR:
Я хочу указать номер и контекст и получить список компонентов/дочерних элементов все, которые имеет указанный номер в этом контексте. Если контекст не указан, запрос должен дать мне все дочерние элементы, независимо от контекста.
Вот рабочий пример с моими примерными данными: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9599f911adb48563c789fa4fc513195d
@Akina Спасибо за ваш отзыв. Я добавил скрипку к моему вопросу.
(1) Ваша версия MySQL действительно 5.7? (2) Предоставьте желаемый результат (в виде отформатированной таблицы) для этих данных. (3) Та же самая рабочий пример в другой онлайн-скрипке, которая кажется мне более полезной: dbfiddle.uk/…
Нет, это не так. Спасибо. Я добавил вашу ссылку в свой вопрос
Это скрипт, который строит простое представление дерева: dbfiddle.uk/… PS. CHAR(255) может быть слишком коротким — при необходимости расширьте его.
Но это берет «все», а не всех детей заданного числа, с учетом заданного контекста.
Просто измените WHERE в якорном подзапросе, например, WHERE `number` = 1234, и вы получите дерево, начинающееся с нужного номера. WHERE EXISTS обеспечивает построение дерева только из корневых узлов.
Использование WHERE number` = 1234 AND NOT EXISTS ...` работает, но WHERE number` = 1236 AND NOT EXISTS ...` не запускается в этом узле
Вы должны заменить и удалить условие НЕ СУЩЕСТВУЕТ, а не добавлять еще одно условие.
Да, это работает, но даже без всего AND NOT EXISTS ( SELECT NULL FROM boms WHERE components.number = boms.child) тоже работает
Сомневаюсь... dbfiddle.uk/…
Плохо, вы хотите добавить свой запрос в качестве ответа, чтобы я мог принять его в. Было бы более полезно для дальнейшего использования. В любом случае: Большое спасибо. Наконец, это решение!
Ответы 1
Это скрипт, который строит простое представление дерева:
WITH RECURSIVE
cte AS (
SELECT `number`, CAST(`number` AS CHAR(255)) path
FROM components
WHERE NOT EXISTS ( SELECT NULL
FROM boms
WHERE components.`number` = boms.child )
UNION ALL
SELECT boms.child, CONCAT(cte.path, '/', boms.child)
FROM cte
JOIN boms ON cte.`number` = boms.parent
)
SELECT `number`, components.name, components.context, boms.count, cte.path
FROM cte
JOIN components USING (`number`)
LEFT JOIN boms ON `number` = boms.child
Если вам нужно дерево для одного определенного узла, измените WHERE в подзапросе привязки, удалите условие WHERE NOT EXISTS и добавьте условие, которое выбирает нужный начальный узел, например WHERE `number` = 1234.
PS. CHAR(255) может быть слишком коротким — при необходимости расширьте его.