Можно ли сделать рекурсивный SQL-запрос?

Ответ 1

В PostgreSQL существует несколько способов сделать то, что вам нужно.

• Если вы можете установить модули, посмотрите на вкладку tablefunc. Он имеет функцию connectby(), которая обрабатывает перемещение деревьев. http://www.postgresql.org/docs/8.3/interactive/tablefunc.html

• Также просмотрите вкладку ltree contrib, которую вы можете адаптировать для использования вашей таблицы: http://www.postgresql.org/docs/8.3/interactive/ltree.html

• Или вы можете пересечь дерево самостоятельно с помощью функции PL/PGSQL.

Что-то вроде этого:

create or replace function example_subtree (integer)
returns setof example as
'declare results record;
         child record;
 begin
  select into results * from example where parent_id = $1;
  if found then
    return next results;
    for child in select id from example
                  where parent_id = $1
      loop
        for temp in select * from example_subtree(child.id)
        loop
          return next temp;
        end loop;
      end loop;
  end if;
  return null;
end;' language 'plpgsql';

select sum(value) as value_sum
  from example_subtree(1234);

Ответ 2

Вот пример script с использованием общего выражения таблицы:

with recursive sumthis(id, val) as (
    select id, value
    from example
    where id = :selectedid
    union all
    select C.id, C.value
    from sumthis P
    inner join example C on P.id = C.parentid
)
select sum(val) from sumthis

script выше создает виртуальную таблицу с именем sumthis, которая имеет столбцы id и val. Он определяется как результат двух выбранных объединений с union all.

Первый select получает корень (where id = :selectedid).

Второй select итеративно следует за дочерними элементами предыдущих результатов, пока нечего возвращать.

Конечный результат может быть обработан как обычная таблица. В этом случае столбцы val суммируются.

Ответ 3

Начиная с версии 8.4, PostgreSQL имеет рекурсивную поддержку запросов для общих выражений таблицы с использованием синтаксиса SQL WITH.

Ответ 4

Если вам требуется переносное решение, которое будет работать на любой ANSI SQL-92 RDBMS, вам нужно будет добавить новый столбец в свою таблицу.

Joe Celko является оригинальным автором подхода Nested Sets для хранения иерархий в SQL. Вы можете Google "вложенные наборы" иерархии, чтобы больше узнать об этом фоне.

Или вы можете просто переименовать parentid в leftid и добавить правду.

Вот моя попытка суммировать вложенные наборы, которая будет очень скудно, потому что я не Джо Целько: SQL - это язык, основанный на наборе, а модель смежности (сохранение родительского идентификатора) НЕ является основанным на наборе представлением иерархии. Поэтому для запроса схемы смежности нет чистого метода на основе набора.

Однако, большинство основных платформ в последние годы внедрили расширения для решения этой точной проблемы. Поэтому, если кто-то отвечает с помощью решения, специфичного для Postgres, используйте это всеми средствами.

Ответ 5

Стандартный способ сделать рекурсивный запрос в SQL рекурсивным CTE. PostgreSQL поддерживает их с 8.4.

В более ранних версиях вы можете написать функцию рекурсивного набора:

CREATE FUNCTION fn_hierarchy (parent INT)
RETURNS SETOF example
AS
$$
        SELECT  example
        FROM    example
        WHERE   id = $1
        UNION ALL
        SELECT  fn_hierarchy(id)
        FROM    example
        WHERE   parentid = $1
$$
LANGUAGE 'sql';

SELECT  *
FROM    fn_hierarchy(1)

См. статью:

• Иерархические запросы в PostgreSQL

Ответ 6

используйте общее выражение таблицы.

Можно указать, что это только SQL Server 2005 или выше. Дейл Раган

здесь статья о рекурсии SqlTeam без общих табличных выражений.

Ответ 7

Если вы используете SQL Server 2005, есть действительно классный способ сделать это, используя Common Table Expressions.

Из-за создания временной таблицы требуется все из-за работы gruntwork и, в основном, позволяет делать все это только с помощью WITH и UNION.

Вот хороший учебник:

http://searchwindevelopment.techtarget.com/tip/0,289483,sid8_gci1278207,00.html

Ответ 8

Следующий код компилируется и проверен ОК.

create or replace function subtree (bigint)
returns setof example as $$
declare
    results record;
    entry   record;
    recs    record;
begin
    select into results * from example where parent = $1;
    if found then
        for entry in select child from example where parent = $1 and child  parent loop
            for recs in select * from subtree(entry.child) loop
                return next recs;
            end loop;
        end loop;
    end if;
    return next results;
end;
$$ language 'plpgsql';

Условие "child < > parent" необходимо в моем случае, потому что узлы указывают на себя.

Удачи:)

Ответ 9

Oracle имеет "НАЧАТЬ С" и "ПОДКЛЮЧИТЬ"

select 
    lpad(' ',2*(level-1)) || to_char(child) s

from 
    test_connect_by 

start with parent is null
connect by prior child = parent;

http://www.adp-gmbh.ch/ora/sql/connect_by.html

Ответ 10

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

общий вопрос SQL

тогда реализация SQL довольно прямолинейна, поскольку SQL'99 допускает линейную рекурсию в спецификации (хотя я считаю, что RDBMSs не реализует стандарт полностью) с помощью инструкции WITH RECURSIVE. Поэтому с теоретической точки зрения мы можем сделать это прямо сейчас.

Ответ 11

Ни один из примеров не работал нормально для меня, поэтому я исправил его следующим образом:

declare
    results record;
    entry   record;
    recs    record;
begin
    for results in select * from project where pid = $1 loop
        return next results;
        for recs in select * from project_subtree(results.id) loop
            return next recs;
        end loop;
    end loop;
    return;
end;

Ответ 12

- это SQL Server? Не могли бы вы написать хранимую процедуру TSQL, которая проходит и объединяет результаты вместе?

Мне также интересно, есть ли способ выполнения SQL-only. Из битов, которые я помню из своего класса географических баз данных, должно быть.

Ответ 13

Я думаю, что в SQL 2008 проще в HierarchyID

Ответ 14

Если вам нужно хранить произвольные графики, а не только иерархии, вы можете нажать Postgres в сторону и попробовать базу данных графа, такую ​​как AllegroGraph

Все в базе данных графа хранится в виде тройки (исходный node, edge, target node) и дает вам поддержку первого класса для управления структурой графа и запроса с помощью SQL-языка.

Он не хорошо интегрируется с чем-то вроде Hibernate или Django ORM, но если вы серьезно относитесь к структурам графов (это дает вам не просто иерархии, такие как модель вложенного набора).

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