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

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

Не знал, есть ли у вас таблица 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. Извини.

- Кевин Фэирчайлд

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

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

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

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;
/

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

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

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

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

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

С помощью этого метода вы можете легко обновить все узлы, зависящие от модифицированного узла K, с помощью примерно N простых запросов SELECT, где N - расстояние K от корневого узла.

Удачи!

Я не помню, под каким вопросом SO я нашел ссылку, но эта статья на sitepoint.com (вторая страница) показывает другой способ хранения иерархических деревьев в таблице, который упрощает поиск всех дочерних узлов или путь к вершине, и тому подобное . Хорошее объяснение с примером кода.

PS. Новинка для StackOverflow, подходит ли вышеприведенное в качестве ответа, или это действительно должен был быть комментарий к вопросу, поскольку это просто указатель на другое решение (не совсем ответ на сам вопрос)?