SQL рекурсивные таблицы

Две вещи приходят на ум:

1 - Вы можете неоднократно внешнее объединение таблица к себе для рекурсивного хождения по дереву, как в:

SELECT *
FROM
  MY_GROUPS MG1
 ,MY_GROUPS MG2
 ,MY_GROUPS MG3
 ,MY_GROUPS MG4
 ,MY_GROUPS MG5
 ,MY_GROUP_MEMBERS MGM
WHERE MG1.PARENT_ID = MG2.UNIQID (+)
  AND MG1.UNIQID = MGM.GROUP_ID (+)
  AND MG2.PARENT_ID = MG3.UNIQID (+)
  AND MG3.PARENT_ID = MG4.UNIQID (+)
  AND MG4.PARENT_ID = MG5.UNIQID (+)
  AND MGM.USER_ID = 9

Это собирается давать Вам результаты как это:

UNIQID PARENT_ID NAME      UNIQID_1 PARENT_ID_1 NAME_1 UNIQID_2 PARENT_ID_2 NAME_2  UNIQID_3 PARENT_ID_3 NAME_3 UNIQID_4 PARENT_ID_4 NAME_4 UNIQID_5 GROUP_ID USER_ID
4      2         Cerepedia 2        1           CATS   1        null        Cerebra null     null        null   null      null       null   8        4        9

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

2 - Единственный другой подход, о котором я знаю, должен создать рекурсивную функцию базы данных и вызов это из кода. У Вас все еще будет некоторый поиск наверху тем путем (т.е. Ваш # запросов все еще будет равен # уровней, которые Вы обходите на дереве), но в целом это должно быть быстрее, так как это все происходит в базе данных.

Я не уверен в MySql, но в Oracle, такая функция была бы подобна этому (необходимо будет изменить имена таблиц и имена полей; я просто копирую что-то, что я сделал в прошлом):

CREATE OR REPLACE FUNCTION GoUpLevel(WO_ID INTEGER, UPLEVEL INTEGER) RETURN INTEGER
IS
BEGIN
  DECLARE
    iResult INTEGER;
    iParent INTEGER;
BEGIN
  IF UPLEVEL <= 0 THEN
    iResult := WO_ID;
  ELSE
    SELECT PARENT_ID
    INTO iParent
    FROM WOTREE
    WHERE ID = WO_ID;    
    iResult := GoUpLevel(iParent,UPLEVEL-1);  --recursive
  END;
  RETURN iResult;
  EXCEPTION WHEN NO_DATA_FOUND THEN
    RETURN NULL;
  END;
END GoUpLevel;
/

Я не думаю, что это может быть выполнено, не используя рекурсию. Можно выполнить его с с единственным MySQL использования хранимой процедуры, но рекурсия не позволяется в хранимых процедурах по умолчанию. Эта статья имеет информацию о том, как включить рекурсию. Я не уверен в том, сколько влияния это оказало бы на стихи производительности, несколько запрашивают подход. MySQL может сделать некоторую оптимизацию хранимых процедур, но иначе я ожидал бы, что производительность будет подобна.

Книги Joe Cleko "SQL для Присяжных острословов" и "Деревьев и Иерархий в SQL для Присяжных острословов" описывают методы, которые избегают рекурсии полностью, при помощи вложенных наборов. Это усложняет обновление, но делает другие запросы (которому обычно была бы нужна рекурсия), сравнительно простой. Существуют некоторые примеры в этой статье, написанной Joe назад в 1996.

Не знал, была ли у Вас таблица Users, таким образом, я добираюсь, список через User_ID сохранил в таблице Group_Member...

SELECT GroupUsers.User_ID,
        (
        SELECT 
            STUFF((SELECT ',' + 
            Cast(Group_ID As Varchar(10))
            FROM Group_Member Member (nolock) 
            WHERE Member.User_ID=GroupUsers.User_ID
        FOR XML PATH('')),1,1,'') 
        ) As Groups
FROM (SELECT User_ID FROM Group_Member GROUP BY User_ID) GroupUsers

Это возвращается:

User_ID    Groups
3          1
4          1
5          1
6          2,4
7          2
9          4
10         1
12         5

Который кажется правильным согласно данным в Вашей таблице. Но не совпадает с Вашим списком математического ожидания (например, Пользователь 9 находится только в одной группе в Ваших данных таблицы, но Вы показываете его в результатах как принадлежащий два),

Править: Блин. Просто замеченный, что Вы используете MySQL. Мое решение было для SQL Server. Прошу прощения.

- Kevin Fairchild

Нет никакого способа сделать это в стандарте SQL, но можно обычно находить определенные для поставщика расширения, например, CONNECT BY в Oracle.

ОБНОВЛЕНИЕ: Как комментарии указывают, это было добавлено в SQL 99.

Уже был подобный поднятый вопрос.

Вот мой ответ (немного отредактирован):

Я не уверен, что понимаю правильно Ваш вопрос, но это могло работать Мое взятие над деревьями в SQL.

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

С этим методом Вы можете легкое обновление все узлы зависеть от измененного узла K с приблизительно N простые запросы ВЫБОРОВ, где N является расстоянием K от корневого узла.

Удачи!

I don't remember which SO question I found the link under, but this article on sitepoint.com (second page) shows another way of storing hierarchical trees in a table that makes it easy to find all child nodes, or the path to the top, things like that. Good explanation with example code.

PS. Newish to StackOverflow, is the above ok as an answer, or should it really have been a comment on the question since it's just a pointer to a different solution (not exactly answering the question itself)?