Помогите составить запрос

АлексейOra92
Дата: 27.09.2004 17:21:45
Уважаемые, Олл

Есть таблица вида:

CREATE TABLE b_ref (
       id_ref               INTEGER NOT NULL,
       id_parent          INTEGER NULL,
       name               VARCHAR2(100) NOT NULL
);
вообщем рекурсия..

Строю дерево:
SELECT b.id_ref,
       b.id_parent,
       b.name, 
       LEVEL lev, 
       SYS_CONNECT_BY_PATH(b.name, '/') path,
       n.cnt
FROM B_REF b, (SELECT id_type, COUNT(*) cnt FROM B_NOMENC GROUP BY id_type) n
WHERE LEVEL = :LEVEL AND b.id_ref = n.id_type(+)
START WITH b.id_ref = 0
CONNECT BY PRIOR b.id_ref = b.id_parent
ORDER SIBLINGS BY b.name

поле cnt - это количество некоторых записей в другой таблице, сответствующие данному элементу дерева..

Помогите дополниь этот запрос:
1. Требуется сделать суммирование для групп.. в группах эта цифра раскладывается по подгруппам и так до самого нижнего уровня..
2. И для каждой записи из b_ref получить номер id_ref верхнего уровня

Жду советов :)
АлексейOra92
Дата: 27.09.2004 17:57:44
Чуть по подробнее:
Допустим есть группа "Продукты" и подгруппы "Молочные продукты" и "Хлебобулочные изделия" и т.д.
есть номенклатура, относящаяся к группам - для "Продукты" - 2 записи, "мол.." - 3, "Хлеб - 4"

Результат выборки должен быть -
Продукты 9
Мол 3
Хлеб 4

Так же требуется поле,значение которого равно id_ref из этой же ветки для которого выполняется условие prior id_parent is null

Воот!!! Желательно все в одном запросе.. :)
Гусена
Дата: 28.09.2004 11:18:40
CREATE TABLE b_ref (
       id_ref     INTEGER NOT NULL,
       id_parent  INTEGER NULL,
       name       VARCHAR2(100) NOT NULL
);

insert into b_ref values (1, null, 'foodstuffs');
   insert into b_ref values (2, 1, 'dairy produce');
     insert into b_ref values (5, 2, 'milk');
     insert into b_ref values (6, 2, 'quark');
   insert into b_ref values (3, 1, 'bakery produce');

не совсем понятно как устроена таблица B_NOMENC, но судя по запросу

SELECT id_type, COUNT(*) cnt FROM B_NOMENC GROUP BY id_type

должно быть нечто подобное

CREATE TABLE B_NOMENC (
       id_type    INTEGER NOT NULL,
       dt         date
);

-- 2 items of the type foodstuffs
insert into B_NOMENC values (1, sysdate);
insert into B_NOMENC values (1, sysdate);

-- 3 items of the type dairy produce
insert into B_NOMENC values (2, sysdate);
insert into B_NOMENC values (2, sysdate);

-- 4 items of the type milk
insert into B_NOMENC values (5, sysdate);
insert into B_NOMENC values (5, sysdate);
insert into B_NOMENC values (5, sysdate);
insert into B_NOMENC values (5, sysdate);

-- 5 items of the type quark
insert into B_NOMENC values (6, sysdate);
insert into B_NOMENC values (6, sysdate);
insert into B_NOMENC values (6, sysdate);
insert into B_NOMENC values (6, sysdate);
insert into B_NOMENC values (6, sysdate);

-- 4 items of the type bakery produce
insert into B_NOMENC values (3, sysdate);
insert into B_NOMENC values (3, sysdate);
insert into B_NOMENC values (3, sysdate);
insert into B_NOMENC values (3, sysdate);

Промежуточный запрос (подзапрос)
select name,
id_ref, id_parent,
count(B_NOMENC.id_type) cnt
from b_ref, B_NOMENC
where b_ref.id_ref = B_NOMENC.id_type(+)
group by id_ref, id_parent, name;

NAME                     ID_REF  ID_PARENT        CNT
-------------------- ---------- ---------- ----------
foodstuffs                    1 NULL                2
dairy produce                 2          1          2
bakery produce                3          1          4
milk                          5          2          4
quark                         6          2          5

АлексейOra92
Чуть по подробнее:
Допустим есть группа "Продукты" и подгруппы "Молочные продукты" и "Хлебобулочные изделия" и т.д.
есть номенклатура, относящаяся к группам - для "Продукты" - 2 записи, "мол.." - 3, "Хлеб - 4"

Результат выборки должен быть -
Продукты 9
Мол 3
Хлеб 4

Судя по описанию, для каждого родителя нужна сумма по всем дочерним элементам плюс сам
родительский элемент.

Окончательный запрос (работает с версии 9)
WITH result_table as
  (select name,
   id_ref, id_parent,
   count(B_NOMENC.id_type) cnt
   from b_ref, B_NOMENC
   where b_ref.id_ref = B_NOMENC.id_type(+)
   group by id_ref, id_parent, name)
select
  LPAD(' ', 2*LEVEL) || name as nm,
  id_ref, id_parent, cnt,
  nvl((select sum(cnt) from result_table rt2 START WITH rt2.id_parent = rt1.id_ref
   CONNECT BY PRIOR rt2.id_ref = rt2.id_parent), 0) + cnt sm
from
  result_table rt1
START WITH id_parent IS NULL
CONNECT BY PRIOR id_ref = id_parent
order siblings by name;

NM                       ID_REF  ID_PARENT        CNT         SM
-------------------- ---------- ---------- ---------- ----------
  foodstuffs                  1 NULL                2         17 -- (2) + (4+2+4+5)
    bakery produce            3          1          4          4 -- (4)
    dairy produce             2          1          2         11 -- (2) + (4+5)
      milk                    5          2          4          4 -- (4)
      quark                   6          2          5          5 -- (5)
АлексейOra92
Дата: 28.09.2004 15:32:48
Благодарю!! Отлично, не нравиться :)