Как создать рекурсивный запрос SQL Oracle?

Question

[eliasum]

Здравствуйте)

Дано три таблицы:

--Таблица каталога продуктов
create table catalog (cid number primary key, -- id раздела
par_cid number references catalog, -- ссылка на родительский раздел
rname varchar2(400), -- наименование раздела
rdescr varchar2(4000), -- описание
rcdate date -- дата создания
);

--Таблица продуктов
create table products (pid number primary key, -- id продукта
rcid number references catalog, -- ссылка на каталог
pname varchar2(500), -- наименование продукта
pdescr varchar2(4000), -- спецификация
punit number references units, -- единица измерения
pper number references persons -- ответственный
);

--Таблица движения продуктов
create table records (rpid number references products, -- продукт
rdate date, -- дата операции
incoming varchar2(2) default '1', -- поступление '1', расход '0'
quantity number, -- количество
rate number -- цена в рублях
);

Требуется написать sql запрос для вывода примерно в таком виде:

<Наименование раздела каталога уровня1> || Поступление. Руб. || Расход. Руб
...
<Наименование раздела каталога уровня(K-1)> || Поступление. Руб. || Расход. Руб
<Наименование раздела каталога уровня(K)> || Поступление. Руб. || Расход. Руб
<Наименование Продукта1 этого раздела> || Поступление. Руб. || Расход. Руб || Поступление. Количество || Расход. Количество || Остаток
...
<Наименование ПродуктаN этого раздела> || Поступление. Руб. || Расход. Руб || Поступление. Количество || Расход. Количество || Остаток
<Наименование раздела каталога уровня(K)> || Поступление. Руб. || Расход. Руб
<Наименование Продукта1 этого раздела> || Поступление. Руб. || Расход. Руб || Поступление. Количество || Расход. Количество || Остаток
...
<Наименование ПродуктаN этого раздела> || Поступление. Руб. || Расход. Руб || Поступление. Количество || Расход. Количество || Остаток
<Наименование раздела каталога уровня1> || Поступление. Руб. || Расход. Руб
... и так далее аналогично

Расчет для разделов должен выполняться в соответствии с иерархией. Количество уровней иерархии не ограничено.

Вывел все каталоги:

SELECT cid, par_cid, rname
FROM catalog
START WITH par_cid is null
CONNECT BY PRIOR cid = par_cid
ORDER BY cid;

Глубже в рекурсию не знаю, как написать запрос. Можете подсказать, как дальше?

Answer 1

[PetrGudym]

Добрый день, elijah eliasum!

Вы все правильно двигаетесь, только в обратную сторону, начиная с листьев.
Воспользуйтесь системными полями sys_connect_by_path, sys_connect_by_root
и после аналитическими функциями Оракла sum() over(), row_number() over()

примерно так:

select *
  from (
       select sum(quantity) over (partition by path_) quantity,
              row_number() over (partition by path_) rn
	          --,h.*, p.*, r.*
        from (
              SELECT cid, par_cid, rname, 
                     sys_connect_by_path(cid,'/') path_,
	                 sys_connect_by_root(cid)     product_cid
                FROM catalog
               START WITH cid in (
		            -- возможно есть способ определить листья 
			        -- без использования дополнительного вызова 
		           select distinct rcid from products
		           ) 
              CONNECT BY PRIOR par_cid = cid
       ) h 
       join products p on (h.product_cid = p.rcid)
       join records r ...
    )
where rn = 1

Answer 2

[Сергей Масленников]

Добрый день, elijah eliasum!

Мне кажется, что Вы не очень хорошо представляете как работает реляционная СУБД в целом и MDL предложение SELECT языка SQL в частности.

Для итога 1-го уровня вида:
<Наименование раздела каталога уровня1> || Поступление. Руб. || Расход. Руб

надо выполнить одно предложение "SELECT"

Для получения результата по виду продукции:

<Наименование ПродуктаN этого раздела> || Поступление. Руб. || Расход. Руб || Поступление. Количество || Расход. Количество || Остаток

надо выполнить другое предложение "SELECT"

А для получения иерархии -- надо делать дополнительную логику на том языке из которого выполняются предложения "SELECT".

И еще, что-то приведенная Вами структура таблиц не соответствует 3 Нормальной Форме - где поля первичного ключа ("ID")?

Как-то так...