ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Иерархический запрос с условиями
20 сообщений из 20, страница 1 из 1
Иерархический запрос с условиями
    #39305613
aidynchik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aidynchik
Участник
Блин ребят, помогите, никак не могу дотукать, застрял что-то.
Есть ирерархическая таблица товаров G_Product, в ней колонки G_Product, G_Product_Par и Name. Уровень вложенности не ограничен.
Нужно вывести иерархию вместе с дочками, причем только те категории и товары, у которых были продажи/приход за данный период времени.
к примеру
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT G.G_PRODUCT_PAR,
       G.G_PRODUCT,
       G.NAME
  FROM G_PRODUCT G
 WHERE (
        (G.G_PRODUCT IN (SELECT SS.G_PRODUCT
                         FROM SKLAD SS
                        WHERE SS.DATE_RECEIPT BETWEEN '01.08.2016' AND '05.09.2016'))
         OR (G.G_PRODUCT IN (SELECT ZZD.G_PRODUCT
                               FROM ZAKAZ_DETAILS ZZD
                              WHERE ZZD.Z_DATE BETWEEN '01.08.2016' AND '05.09.2016')))



как к ним родителей прикрутить? Чтобы отчет вышел вида
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
Шампуни
 - Немецкие
   - Фирма 1
     - Товар 1
     - Товар 2
   - Фирма 2
     - Товар 3
     - Товар 4
 - Голландские
   - Товар 5
   - Товар 6

Отступы необязательно, главное принцип.
Пытался сделать вот так, но иерархия снизу вверх идет, и потом после каждого товара добавляется его иерархия
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
SELECT *
  FROM (WITH RECURSIVE TREE
        AS (SELECT G.G_PRODUCT_PAR,
                   G.G_PRODUCT,
                   G.NAME
              FROM G_PRODUCT G
             WHERE (
                    (G.G_PRODUCT IN (SELECT SS.G_PRODUCT
                                     FROM SKLAD SS
                                    WHERE SS.DATE_RECEIPT BETWEEN '01.08.2016' AND '05.09.2016'))
                     OR (G.G_PRODUCT IN (SELECT ZZD.G_PRODUCT
                                           FROM ZAKAZ_DETAILS ZZD
                              WHERE ZZD.Z_DATE BETWEEN '01.08.2016' AND '05.09.2016')))
            UNION ALL
            SELECT G.G_PRODUCT_PAR,
                   G.G_PRODUCT,
                   G.NAME
              FROM G_PRODUCT G INNER JOIN TREE PRIOR ON PRIOR.G_PRODUCT_PAR = G.G_PRODUCT
             WHERE G.IS_CATEGORY = 1
              )
           SELECT * FROM TREE) SS
...
Рейтинг: 0 / 0
08.09.2016, 08:12
| Ответить | Цитировать | Написать  
Иерархический запрос с условиями
    #39305622
Гхостик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гхостик
Гость
Сделай distinct и обойди данные ещё раз сверху-вниз для сортировки.
...
Рейтинг: 0 / 0
08.09.2016, 08:59
| Ответить | Цитировать | Написать  
Иерархический запрос с условиями
    #39306010
KreatorXXI
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KreatorXXI
Участник
aidynchik,

Не просто это. Почитай вот здесь - http://gsbelarus.com/pw/articles/post/derev-ia-v-sql/.
...
Рейтинг: 0 / 0
08.09.2016, 16:28
| Ответить | Цитировать | Написать  
Иерархический запрос с условиями
    #39306067
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
aidynchik,

Проблема в том что ты начинаешь обход дерева не от корневых групп, т.е. от групп у которых G_PRODUCT_PAR IS NULL

маленький пример на псевдотаблицах

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
with recursive
sales (goods_id) as (
  select 8 from rdb$database union all
  select 11 from rdb$database union all
  select 6 from rdb$database union all
  select 5 from rdb$database
),
goods (id, parent_id, name, is_group) as (
  select  1, null, 'Шампуни', true from rdb$database union all
  select  2, 1, 'Немецкие', true from rdb$database union all
  select  3, 1, 'Голландские', true from rdb$database union all
  select  4, 2, 'Фирма 1', true from rdb$database union all
  select  5, 4, 'Товар 1', false from rdb$database union all
  select  6, 4, 'Товар 2', false from rdb$database union all
  select  7, 2, 'Фирма 2', true from rdb$database union all
  select  8, 7, 'Товар 3', false from rdb$database union all
  select  9, 7, 'Товар 4', false from rdb$database union all
  select 10, 3, 'Товар 5', false from rdb$database union all
  select 11, 3, 'Товар 6', false from rdb$database
),
r (id, parent_id, name, is_group, r_level) as (
  -- начинаем обход с групп
  select id, parent_id, name, is_group, 0 from goods where parent_id is null
  union all
  select goods.id, goods.parent_id, goods.name, is_group, r.r_level+1
  from r join goods on goods.parent_id = r.id
  -- если группа пропускаем, иначе проверяем наличие продаж
  where goods.is_group or exists(select *  from sales where goods.id = sales.goods_id)
)
select
  r.id,
  r.parent_id,
  r.name,
  r.is_group,
  lpad('', 4*r_level, '-') || r.name as r_name,
  r.r_level
from r



здесь одна проблема группы будут выводится даже если по её товарам нет продаж
...
Рейтинг: 0 / 0
08.09.2016, 17:27
| Ответить | Цитировать | Написать  
Иерархический запрос с условиями
    #39306093
aidynchik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aidynchik
Участник
Симонов Денис,

так я потому и начал с конца, чтобы лишние категории не выводить =)
...
Рейтинг: 0 / 0
08.09.2016, 17:55
| Ответить | Цитировать | Написать  
Иерархический запрос с условиями
    #39306158
hvlad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hvlad
Участник
aidynchik
Код: plsql
1.
2.
SELECT *
  FROM (WITH RECURSIVE

Это надо же настолько не понимать CTE и так изуродовать запрос :'(
...
Рейтинг: 0 / 0
08.09.2016, 20:19
| Ответить | Цитировать | Написать  
Иерархический запрос с условиями
    #39306172
aidynchik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aidynchik
Участник
hvlad,

лучше бы дельным советом помогли, не лень же писать свое мнение обо мне
...
Рейтинг: 0 / 0
08.09.2016, 21:04
| Ответить | Цитировать | Написать  
Иерархический запрос с условиями
    #39306178
hvlad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hvlad
Участник
aidynchik,

тебе тут достаточно помогают.
А вот других на твоём примере поучить - этого не было.
Я восполнил пробел.
...
Рейтинг: 0 / 0
08.09.2016, 21:43
| Ответить | Цитировать | Написать  
Иерархический запрос с условиями
    #39306181
aidynchik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aidynchik
Участник
hvlad,

заранее извинте, но вы других поучили бы гораздо больше дав дельный совет, а не излагая философские фразы
...
Рейтинг: 0 / 0
08.09.2016, 21:52
| Ответить | Цитировать | Написать  
Иерархический запрос с условиями
    #39306192
aidynchik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aidynchik
Участник
ГхостикСделай distinct и обойди данные ещё раз сверху-вниз для сортировки.
что значит обойти данные еще раз?
...
Рейтинг: 0 / 0
08.09.2016, 22:19
| Ответить | Цитировать | Написать  
Иерархический запрос с условиями
    #39306200
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
aidynchikГхостикСделай distinct и обойди данные ещё раз сверху-вниз для сортировки.
что значит обойти данные еще раз?

даже если тебе это удастся производительность будет ниже плинтуса.
...
Рейтинг: 0 / 0
08.09.2016, 22:36
| Ответить | Цитировать | Написать  
Иерархический запрос с условиями
    #39306251
aidynchik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aidynchik
Участник
Симонов Денис,

и что эта задача никак не решается что ли... она же довольно тривиальная - вытащить все товары с категориями проданные за месяц
...
Рейтинг: 0 / 0
09.09.2016, 06:07
| Ответить | Цитировать | Написать  
Иерархический запрос с условиями
    #39306272
aidynchik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aidynchik
Участник
В общем как-то так получилось
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
WITH RECURSIVE TREE
  AS (SELECT T.G_PRODUCT,
           T.NAME NAME,
           replace(T.NAME, ' ', '') PATH,       --для сортировки
           '-'||CAST(T.G_PRODUCT AS VARCHAR(100))||'-' PPA,
           T.IS_CATEGORY
      FROM G_PRODUCT T
     WHERE T.G_PRODUCT_PAR IS NULL
    UNION ALL
    SELECT T.G_PRODUCT,
           T.NAME NAME,
           GG.PATH||replace(T.NAME, ' ', '') PATH,       --для сортировки
           GG.PPA||T.G_PRODUCT||'-' PPA,
           T.IS_CATEGORY
      FROM G_PRODUCT T INNER JOIN TREE GG ON GG.G_PRODUCT = T.G_PRODUCT_PAR
     WHERE ((T.IS_CATEGORY = 1)
            OR
            ((
                (T.G_PRODUCT IN (SELECT SS.G_PRODUCT
                                 FROM SKLAD SS
                                WHERE SS.DATE_RECEIPT BETWEEN '01.08.2016' AND '05.09.2016'))
                 OR (T.G_PRODUCT IN (SELECT ZZD.G_PRODUCT
                                           FROM ZAKAZ_DETAILS ZZD
                              WHERE ZZD.Z_DATE BETWEEN '01.08.2016' AND '05.09.2016'))
             ) AND T.IS_CATEGORY = 0)
           )
    )
 SELECT * FROM TREE TT
  WHERE IS_CATEGORY = 0
 UNION ALL
 SELECT DISTINCT GPP.G_PRODUCT, GPP.NAME, R.PATH, '' PPA, GPP.IS_CATEGORY
   FROM G_PRODUCT GPP, TREE R
  WHERE GPP.IS_CATEGORY = 1
    AND (SELECT LIST(PPA) FROM TREE WHERE IS_CATEGORY = 0) CONTAINING '-'||GPP.G_PRODUCT||'-'
    AND GPP.G_PRODUCT = R.G_PRODUCT
ORDER BY 3, 1
...
Рейтинг: 0 / 0
09.09.2016, 07:33
| Ответить | Цитировать | Написать  
Иерархический запрос с условиями
    #39306289
exists
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
exists
Гость
вот эта часть
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
  WHERE ((T.IS_CATEGORY = 1)
            OR
            ((
                (T.G_PRODUCT IN (SELECT SS.G_PRODUCT
                                 FROM SKLAD SS
                                WHERE SS.DATE_RECEIPT BETWEEN '01.08.2016' AND '05.09.2016'))
                 OR (T.G_PRODUCT IN (SELECT ZZD.G_PRODUCT
                                           FROM ZAKAZ_DETAILS ZZD
                              WHERE ZZD.Z_DATE BETWEEN '01.08.2016' AND '05.09.2016'))
             ) AND T.IS_CATEGORY = 0)
           )



так не лучше будет?
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
  WHERE T.IS_CATEGORY = 1 OR EXISTS (
     SELECT NULL
     FROM SKLAD SS
     WHERE SS.DATE_RECEIPT BETWEEN '01.08.2016' AND '05.09.2016' AND SS.G_PRODUCT = T.G_PRODUCT

     UNION ALL

     SELECT NULL
     FROM ZAKAZ_DETAILS ZZD
     WHERE ZZD.Z_DATE BETWEEN '01.08.2016' AND '05.09.2016' AND ZZD.G_PRODUCT = T.G_PRODUCT
  )
...
Рейтинг: 0 / 0
09.09.2016, 08:04
| Ответить | Цитировать | Написать  
Иерархический запрос с условиями
    #39306290
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
aidynchik,

Я не говорил, что не решается. Я говорил о низкой скорости подобного рода запросов
...
Рейтинг: 0 / 0
09.09.2016, 08:05
| Ответить | Цитировать | Написать  
Иерархический запрос с условиями
    #39306300
aidynchik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aidynchik
Участник
exists,

конечно лучше, спасибо!

Вообще всем спасибо за помощь и направления
...
Рейтинг: 0 / 0
09.09.2016, 08:14
| Ответить | Цитировать | Написать  
Иерархический запрос с условиями
    #39306307
Гхостик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гхостик
Гость
Симонов ДенисЯ говорил о низкой скорости подобного рода запросовЕсли объем данных будет большой, можно положить отфильтрованное дерево в gtt с индексами и обходить сверху-вниз уже её.
...
Рейтинг: 0 / 0
09.09.2016, 08:22
| Ответить | Цитировать | Написать  
Иерархический запрос с условиями
    #39306309
aidynchik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aidynchik
Участник
ГхостикСимонов ДенисЯ говорил о низкой скорости подобного рода запросовЕсли объем данных будет большой, можно положить отфильтрованное дерево в gtt с индексами и обходить сверху-вниз уже её.

можете пояснить для особо тупых, что значит обходить сверху-вниз
...
Рейтинг: 0 / 0
09.09.2016, 08:25
| Ответить | Цитировать | Написать  
Иерархический запрос с условиями
    #39306348
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
aidynchik,

у тебя обход снизу вверх, т.е. от листьев к корню, в моём примере сверху вниз.

Гхостик предлагает удалить дубликаты корня и ветвей в твоём изначальном дереве с помощью distinct и засунуть выборку в GTT.
А потом по этой GTT сделать ещё один рекурсивный запрос уже сверху вниз для получения правильного порядка. В его решении два преимущества: во-первых GTT можно сделать индексированным, а во-вторых это поможет избежать многократного перевыполнения запроса строящего твоё изначальное дерево.
...
Рейтинг: 0 / 0
09.09.2016, 09:30
| Ответить | Цитировать | Написать  
Иерархический запрос с условиями
    #39306526
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
aidynchik,

вот это место уже говорит о том, что будут тормоза, и что надо бы движение товаров (SKLAD и ZAKAZ_DETAILS) слить в одну таблицу.

Код: sql
1.
2.
3.
4.
5.
6.
 WHERE (
        (G.G_PRODUCT IN (SELECT SS.G_PRODUCT
                         FROM SKLAD SS
...))
         OR (G.G_PRODUCT IN (SELECT ZZD.G_PRODUCT
                               FROM ZAKAZ_DETAILS ZZD
...
Рейтинг: 0 / 0
09.09.2016, 12:40
| Ответить | Цитировать | Написать  
20 сообщений из 20, страница 1 из 1
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Иерархический запрос с условиями
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=40&msg=39306251&tid=1561972]:
 0ms
get settings:
 12ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 55ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 53ms
get tp. blocked users:
 1ms
others: 17ms
total:  169ms
созд. / загр.: 169ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]