Как да избирам рекурсивно от резултатите от заявката?

Как може да се използват повторно резултатите от заявката по най-ефективния начин?

Имам две таблици: елементи и отношения. Елементът може да бъде самотен артикул или може да бъде дете на друг артикул. Връзките се поддържат в таблицата на релациите. Елементът се идентифицира уникално чрез колони depNo, itemNo. Ето примерния набор от данни:

create table items
(
itemId int identity (1,1) not null ,
depNo int not null,
itemNo int not null,
name varchar(50),
class int not null, -- 0 - unknown class, 1 - child item
constraint pk_depNo_itemNo primary key (depNo, itemNo)
);

create table relations
(
relId int identity (1,1) not null,
pDepNo int not null,
pItemNo int not null,
cDepNo int not null,
cItemNo int not null,
constraint pk_parent_child primary key (pDepNo, pItemNo, cDepNo, cItemNo)
);

insert into items values (1, 1, 'M1CItem1', 1);
insert into items values (1, 2, 'M1CItem2', 1);
insert into items values (1, 3, 'M1CItem3', 1);
insert into items values (2, 1, 'Master1', 0);
insert into items values (2, 2, 'LItem1', 0);
insert into items values (2, 3, 'LItem2', 0);
insert into items values (2, 4, 'LItem3', 0);
insert into items values (2, 5, 'Master2', 0);
insert into items values (2, 6, 'M2CItem1', 1);
insert into items values (2, 7, 'M2CItem1', 1);

insert into relations values (2, 1, 1, 1);
insert into relations values (2, 1, 1, 2);
insert into relations values (2, 1, 1, 3);
insert into relations values (2, 5, 2, 6);
insert into relations values (2, 5, 2, 7);

Следната заявка избира всички елементи, отговарящи на критериите на заявката, или техните родители, ако елементът е дете:

with qRes as (
select depNo, itemNo, name, class, pDepNo, pItemNo from items
left outer join relations
on depNo = cDepNo
and itemNo = cItemNo
where name like '%Item1'
)
-- select all results where item is not a child
select depNo, itemNo, name, class from qRes where class <> 1
union
-- select all parents of the children
select B.depNo, B.itemNo, B.name, B.class from qRes A
inner join items B
on A.pDepNo = B.depNo
and A.pItemNo = B.itemNo;

Изпълнената заявка ще върне:

depNo   itemNo  name    class
2           1   Master1 0
2           2   LItem1  0
2           5   Master2 0

Има ли по-добри начини за справяне с подобен проблем?


sql sql-server tsql
person Robertas    schedule 10.07.2012    source източник

Отговори (2)


arrow_upward
1
arrow_downward

Ако имате само едно ниво на рекурсия, вашият подход е наред, ако ще имате много нива в йерархията, тогава може да обмислите използването на рекурсивен подход.

напр. ако променя една от вашите връзки:

UPDATE  Relations
SET     pItemNo = 6
WHERE   cItemNo = 7

След това прави този ред {DepNo: 2, ItemNo: 7, name: M2CItem1} дъщерен на {DepNo: 2, ItemNo: 6, name: M2CItem1}, който от своя страна е дъщерен на {DepNo: 2, ItemNo: 5, name: Master2}

Следното ще върне както M2CItem1, така и Master2

;WITH CTE AS
(   SELECT  depNo, itemNo, name, class, pDepNo, pItemNo, 1 [RecursionLevel]
    FROM    items
            LEFT JOIN relations
                ON DepNo = cDepNo
                AND ItemNo = cItemNo
    WHERE   name like '%Item1'
    UNION ALL
    SELECT  i.depNo, i.itemNo, i.name, i.class, r.pDepNo, r.pItemNo, RecursionLevel + 1
    FROM    CTE i
            INNER JOIN relations r
                ON i.pDepNo = r.cDepNo
                AND i.pItemNo = r.cItemNo
)
SELECT  DISTINCT c.DepNo, c.ItemNo, i.Name, i.Class
FROM    CTE c
        INNER JOIN Items i
            ON COALESCE(c.pDepNo, c.DepNo) = i.DepNo
            AND COALESCE(c.pItemNo, c.ItemNo) = i.ItemNo

Ако обаче искате да върнете само най-горния родител, можете да използвате:

;WITH CTE AS
(   SELECT  depNo, itemNo, name, class, pDepNo, pItemNo, 1 [RecursionLevel]
    FROM    items
            LEFT JOIN relations
                ON DepNo = cDepNo
                AND ItemNo = cItemNo
    WHERE   name like '%Item1'
    UNION ALL
    SELECT  i.depNo, i.itemNo, i.name, i.class, r.pDepNo, r.pItemNo, RecursionLevel + 1
    FROM    CTE i
            INNER JOIN relations r
                ON i.pDepNo = r.cDepNo
                AND i.pItemNo = r.cItemNo
), CTE2 AS
(   SELECT  c.DepNo, c.ItemNo, i.Name, i.Class, RecursionLevel, MAX(RecursionLevel) OVER(PARTITION BY c.DepNo, c.ItemNo) [MaxRecursionLevel]
    FROM    CTE c
            INNER JOIN Items i
                ON COALESCE(c.pDepNo, c.DepNo) = i.DepNo
                AND COALESCE(c.pItemNo, c.ItemNo) = i.ItemNo
)
SELECT  DepNo, ItemNo, Name, Class
FROM    CTE2
WHERE   Recursionlevel = maxRecursionLevel

Това ще върне само {DepNo: 2, ItemNo: 5, name: Master2} за ред {DepNo: 2, ItemNo: 7, name: M2CItem1}, тъй като това е родителят на неговия родител.

Работни примери за SQL Fiddle

Като настрана, мисля, че трябва да преразгледате схемата си? За какво е вашата колона за самоличност, ако правите ItemNo и DepNo съставен първичен ключ? Вероятно трябва да изберете едното или другото, а не и двете.

person GarethD    schedule 10.07.2012

arrow_upward
0
arrow_downward

Можете да направите рекурсията дете-родител в самия израз на общата таблица с оператор UNION ALL, но вашият подход е почти там.

Може да искате да разгледате типа данни Hierarchy, ако имате възможност да промените таблиците.

person podiluska    schedule 10.07.2012