Моделирование CONNECT BY ПРИОР ORACLE в SQL SERVER

Ответ 1

Стандартный способ SQL для реализации рекурсивных запросов, реализованный, например, IBM DB2 и SQL Server - это предложение WITH. См. эту статью для одного примера перевода CONNECT BY в WITH (технически рекурсивный CTE) - пример для DB2, но Я считаю, что он будет работать и на SQL Server.

Изменить: по-видимому, для исходного запроса требуется конкретный пример, здесь один из сайта IBM, чей URL я уже дал. Учитывая таблицу:

CREATE TABLE emp(empid  INTEGER NOT NULL PRIMARY KEY,
                 name   VARCHAR(10),
                 salary DECIMAL(9, 2),
                 mgrid  INTEGER);

где mgrid ссылается на менеджера-менеджера empid, задача состоит в том, чтобы получить имена всех, кто прямо или косвенно сообщает Joan. В Oracle это простой CONNECT:

SELECT name 
  FROM emp
  START WITH name = 'Joan'
  CONNECT BY PRIOR empid = mgrid

В SQL Server, IBM DB2 или PostgreSQL 8.4 (как и в стандарте SQL, для чего это стоит;-), совершенно эквивалентное решение - это рекурсивный запрос (более сложный синтаксис, но, фактически, еще больше мощность и гибкость):

WITH n(empid, name) AS 
   (SELECT empid, name 
    FROM emp
    WHERE name = 'Joan'
        UNION ALL
    SELECT nplus1.empid, nplus1.name 
    FROM emp as nplus1, n
    WHERE n.empid = nplus1.mgrid)
SELECT name FROM n

Предложение Oracle START WITH становится первым вложенным SELECT, базовым регистром рекурсии, как UNION ed с рекурсивной частью, которая является просто еще одним SELECT.

Особый вкус SQL Server WITH, конечно, задокументирован на MSDN, который также дает рекомендации и ограничения для использования этого ключевого слова, а также несколько примеров.

Ответ 2

@Alex Martelli Ответ велик! Но он работает только для одного элемента во времени (WHERE name = 'Joan') Если вы выведете предложение WHERE, запрос вернет все корневые строки вместе...

Я немного изменил свою ситуацию, поэтому он может отображать все дерево таблицы.

определение таблицы:

CREATE TABLE [dbo].[mar_categories] ( 
    [category]  int IDENTITY(1,1) NOT NULL,
    [name]      varchar(50) NOT NULL,
    [level]     int NOT NULL,
    [action]    int NOT NULL,
    [parent]    int NULL,
    CONSTRAINT [XPK_mar_categories] PRIMARY KEY([category])
)

(level - это буквально уровень категории 0: корень, 1: первый уровень после root,...)

и запрос:

WITH n(category, name, level, parent, concatenador) AS 
(
    SELECT category, name, level, parent, '('+CONVERT(VARCHAR (MAX), category)+' - '+CONVERT(VARCHAR (MAX), level)+')' as concatenador
    FROM mar_categories
    WHERE parent is null
        UNION ALL
    SELECT m.category, m.name, m.level, m.parent, n.concatenador+' * ('+CONVERT (VARCHAR (MAX), case when ISNULL(m.parent, 0) = 0 then 0 else m.category END)+' - '+CONVERT(VARCHAR (MAX), m.level)+')' as concatenador
    FROM mar_categories as m, n
    WHERE n.category = m.parent
)
SELECT distinct * FROM n ORDER BY concatenador asc

(Вам не нужно конкатенировать поле level, я сделал это только для чтения)

ответ для этого запроса должен выглядеть примерно так:

Я надеюсь, что это поможет кому-то!

теперь, мне интересно, как это сделать в MySQL... ^^

Ответ 3

Я не использовал соединение ранее, но быстрый поиск показывает, что он используется для древовидных структур. В SQL Server вы используете общие выражения таблиц для получения аналогичной функциональности.