Как создать View с рекурсией

← →
Ольга (2005-09-20 11:33) [0]

Есть 2 таблицы.
Иерархический справочник (MAX(Level) - количество уровней):
CREATE TABLE [dbo].[SHEMA] ( [ID] [int] NOT NULL , [ID_Parent] [int] NULL , [Level] [int] NOT NULL , [NAME] [varchar] [50] NULL ) ON [PRIMARY]
Таблица данных по ID:
CREATE TABLE [dbo].[DT_30] ( [DT] [datetime] NOT NULL , [ID] [int] NOT NULL , [GEN] [float] NULL ) ON [PRIMARY]
Нужно найти суммы GEN для всех ID. В таблицу DT_30 пишутся только данные нижнего уровня, т.е. те из которых по id_parent можно сложить все данные вверх по иерархии.
CREATE VIEW dbo.VT_30 (DT, ID, GEN) AS SELECT DT, a.ID_PARENT, SUM(ISNULL(GEN, 0)) FROM SHEMA a, DT_30 b WHERE a.id=b.id GROUP BY DT, a.ID_PARENT
С помощью View VT_30 я получаю только на одну ступеньку вверх. Как сделать View (именно View - так поставлена задача), в котором просчитается все до верхнего уровня?

← →
Desdechado © (2005-09-20 11:44) [1]

написать ХП типа SELECT, на основе которой сделать VIEW

← →
Nikolay M. © (2005-09-20 12:41) [2]

> Desdechado © (20.09.05 11:44) [1]
> написать ХП типа SELECT, на основе которой сделать VIEW

Только не ХП, а функцию. Из вьюхи нельзя вызвать ХП.

← →
Desdechado © (2005-09-20 15:57) [3]

я по привычке про FB подумал :)
в Oracle тоже функция нужна
но суть все равно та же

← →
Val © (2005-09-20 16:01) [4]

гхм...у нее MS SQL, насколько я вижу.

← →
Ольга (2005-09-20 16:23) [5]

Братцы, два часа ломаю голову над функцией - мозгов не хватает, помогите.
Дайте хоть какую-нибудь "рыбу", не врубаюсь.

интербейз-вариант ХП получения потомков пробегал здесь когда-то:

create procedure sp_getchildren(selfid/*, selflevel*/)
returns(id, parentid, name/*, lev*/)
as
begin
for
select id, parentid, name
from items
where parentid = :selfid
order by name
into :id, :parentid, :name
do
begin
/* level = selflevel + 1;*/
suspend;

for
select id, parentid, name/*, lev*/
from sp_getchildren(:id/*, :lev/*)
order by name
into :id, :parentid, :name/*, :lev*/
do
suspend;
end
end

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

CREATE FUNCTION dbo.uf_GetSubPortfoliosIDByPortfolio (@PortfolioID INT, @PortfolioTypeID INT, @PortfolioName VARCHAR(255), @PortfolioBriefName VARCHAR(255), @PortfolioTypeName VARCHAR(255)) RETURNS @ret TABLE (PortfolioID INT PRIMARY KEY) AS BEGIN DECLARE @cnt INT IF (@PortfolioID IS NOT NULL) INSERT INTO @ret (PortfolioID) VALUES (@PortfolioID) .... SELECT @cnt = count(1) FROM @ret WHILE (1 <> 0) BEGIN INSERT INTO @ret SELECT ap.SubPortfolioID FROM tAccountPortfolio ap WHERE ap.PortfolioID IN (SELECT PortfolioID FROM @ret) AND ap.SubPortfolioID IS NOT NULL AND NOT EXISTS (SELECT 1 FROM @ret r WHERE r.PortfolioID = ap.SubPortfolioID) GROUP BY ap.SubPortfolioID IF ((SELECT count(1) FROM @ret) = @cnt) BREAK ELSE SELECT @cnt = count(1) FROM @ret END RETURN END

← →
Ольга (2005-09-21 11:24) [8]

Большое спасибо. Все получилось.

Как создать View с рекурсией Найти похожие ветки