Получение данных иерархии из таблиц саморегуляции

Скажем, у вас есть следующая таблица:

items(item_id, item_parent)

... и это таблица саморегуляции - item_parent относится к item_id.

Какой SQL-запрос вы бы использовали для выбора всех элементов в таблице вместе с их глубиной, где глубина элемента - это сумма всех родителей и великих родителей этого элемента.

Если ниже приведено содержимое таблицы:

item_id     item_parent
----------- -----------
1           0          
2           0            
3           2          
4           2          
5           3

... запрос должен получить следующий набор объектов:

{ "item_id": 1, "глубина" : 0}
{ "Item_id" : 2, "глубина" : 0}
{ "Item_id" : 3, "глубина" : 1}
{ "Item_id" : 4, "глубина" : 1}
{ "item_id": 5, "глубина" : 2}

P.S. Я ищу поддерживаемый MySQL подход.

Ответы

Ответ 1

Если база данных SQL 2005/2008, то...

Самый простой способ получить это - использовать CTE (Common Table Expression), предназначенное для рекурсии.

 WITH myCTE (Item_id, Depth)
 AS
 (
    Select Item_ID, 0 as Depth From yourTable where Item_Parent=0
    Union ALL
    Select yourTable.Item_ID, Depth + 1 
    From yourTable 
    inner join myCte on yourTable.item_Parent = myCte.Item_Id
 )

 Select Item_id, Depth from myCTE

Выход выглядит следующим образом:

Item_Id  Depth
    1   0
    2   0
    3   1
    4   1
    5   2

Оттуда вы можете отформатировать его как хотите.

Ответ 2

На веб-сайте mysql есть хорошая техническая статья об иерархических данных в MySql: Управление иерархическими данными в MySQL - вы можете найти несколько подробных решений с поддержкой и недостатками.

Особенно интересна для вас роль "Вложенная модель набора" и "Поиск глубины узлов".

Ответ 3

Oracle имеет очень удобный синтаксис для извлечения таких иерархических данных, как это:

select
    item_id,
    item_parent,
    level as depth
from
    items
connect by
    prior item_id = item_parent
start with
    item_parent not in (select item_id from items)

Это начинается с корневых узлов ваших деревьев как элементов, чей элемент item_parent не существует в таблице как item_id, и выбирает всех дочерних узлов этих узлов вместе с их глубиной в дереве.

Ответ 5

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

http://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/

Возможно, эти ссылки могут вам помочь. Если вы найдете хорошее решение - напишите здесь. мне не разрешено размещать более 1 ссылку - я добавлю некоторые из следующих сообщений