Выполнение рекурсивного запроса с помощью не рекурсивных функций / Oracle

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Выполнение рекурсивного запроса с помощью не рекурсивных функций

25 сообщений из 38, страница 1 из 2

все

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39397604

Person2713

Гость

Всем привет.
Имеется следующее задание:
Для каждого сотрудника выбрать всех его начальников по иерархии.
Вывести поля: код сотрудника, имя сотрудника (фамилия + имя через пробел),
код начальника, имя начальника (фамилия + имя через пробел), кол-во промежуточных
начальников между сотрудником и начальником из данной строки выборки. Если у какого-то
сотрудника есть несколько начальников, то для данного сотрудника в выборке должно быть
несколько строк с разными начальниками. Упорядочить по коду сотрудника, затем по уровню
начальника (первый – непосредственный начальник, последний – руководитель организации).

Знаю как его выполнить с помощью рекурсивного запроса:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

select  e.employee_id,
        e.last_name || ' ' || e.first_name as employee_name,
        CONNECT_BY_ROOT e.employee_id as manager_id,
        CONNECT_BY_ROOT (e.last_name || ' ' || e.first_name) as manager_name,
        level - 2 as pathlen,
        sys_connect_by_path(e.last_name || ' ' || e.first_name, '/') as path,
        level
  from  employees e
  where level > 1
  connect by e.manager_id = prior(e.employee_id)
  order by e.employee_id, 
           level
;

но не могу понять как это задание можно выполнить без рекурсивного запроса.
Подскажите пожалуйста в какую сторону копать.

...

Рейтинг:

0 / 0

02.02.2017, 21:04

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39397623

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

Person2713Знаю как его выполнить с помощью рекурсивного запроса:
но не могу понять как это задание можно выполнить без рекурсивного запроса.

Твой запрос нe рекурсивный а иерархический. Ну а без иерархического/рекурсивного запроса если нaриcовать пaру функций но внутри их все равно придется ползать от сотруника к начальнику в цикле что по-сути та-же иерархия/рекурсия.

SY.

...

Рейтинг:

0 / 0

02.02.2017, 21:38

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39397668

dbms_photoshop

Участник

Откуда: sqlmdx.net

Сообщения: 5 140

Рейтинг: 0 / 0

Person2713,

Если для получения родительских записей нельзя использовать connect by и recursive subquery factoring,
то задачу можно решить итеративной моделью (для общего случая) или pattern matching (при условии что айдишники увеличиваются с увеличением уровня).

Код: 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.

SQL> with t(id, id_parent) as
  2  (
  3  select 1, 0 from dual
  4  union all select 11, 10 from dual
  5  union all select 2, 1 from dual
  6  union all select 3, 1 from dual
  7  union all select 4, 2 from dual
  8  )
  9  select *
 10  from t
 11  match_recognize
 12  (
 13    order by id desc
 14    measures classifier() as cls
 15    all rows per match
 16    pattern (start_ (parent_|{-dummy-})*)
 17    define
 18      start_ as start_.id = 4,
 19      parent_ as last(start_.id_parent) = parent_.id or prev(parent_.id_parent,1) = parent_.id
 20  ) mr;

        ID CLS                             ID_PARENT
---------- ------------------------------ ----------
         4 START_                                  2
         2 PARENT_                                 1
         1 PARENT_                                 0

...

Рейтинг:

0 / 0

02.02.2017, 22:42

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39397693

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

dbms_photoshopзадачу можно решить итеративной моделью

А итеративнaя модель это не рекурсия?
Через match_recognize врядли получится - не ползает он через ту-же строку дважды.

SY.

...

Рейтинг:

0 / 0

02.02.2017, 23:22

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39397752

Person2713

Гость

SY,
помой-му иерархический и рекурсивный запрос одно и тоже. Я вроде читал, что Oracle вместо рекурсивный запрос использует иерархический запрос. Поправьте меня если ошибаюсь.

...

Рейтинг:

0 / 0

03.02.2017, 08:00

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39397754

Person2713

Гость

dbms_photoshop,

То что вы написали не является ли "рекурсивным запросом с подзапросом with" ( http://citforum.ru/database/oracle/recursive/ )

...

Рейтинг:

0 / 0

03.02.2017, 08:05

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39397756

Добрый Э - Эх

Гость

Person2713То что вы написали не является ли "рекурсивным запросом с подзапросом with" ну покажи нам, где там якорная часть, где рекурсивная...

...

Рейтинг:

0 / 0

03.02.2017, 08:08

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39398020

dbms_photoshop

Участник

Откуда: sqlmdx.net

Сообщения: 5 140

Рейтинг: 0 / 0

SYdbms_photoshopзадачу можно решить итеративной моделью

А итеративнaя модель это не рекурсия?
Через match_recognize врядли получится - не ползает он через ту-же строку дважды.

SY.Итеративная модель - это цикл while (если речь про обход дерева).
Кстати, любую рекурсию можно реализовать через не рекурсию.
Я даже устраивал троллинг на эту тему в форуме работа 13199084 , но не взлетело - там любители поговорить, а не сравнивать производительность.
Ползать в две стороны не выйдет, но я написал в скобочках ограничения для решения выше.

PS. Мы своими разговорчиками только запутаем ТС. У него и так полная каша в голове.

...

Рейтинг:

0 / 0

03.02.2017, 12:14

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39398880

Person2713

Гость

объясните пожалуйста чем отличаются иерархические запросы от рекурсивных в oracle

...

Рейтинг:

0 / 0

05.02.2017, 16:41

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39398881

dbms_photoshop

Участник

Откуда: sqlmdx.net

Сообщения: 5 140

Рейтинг: 0 / 0

Person2713объясните пожалуйста чем отличаются иерархические запросы от рекурсивных в oracleОткрываешь синтаксис SELECT и на странице Ctr + F по словам "recursive" и "hierarchical".

...

Рейтинг:

0 / 0

05.02.2017, 16:46

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39398902

Person2713

Гость

Спасибо dbms_photoshop,

...

Рейтинг:

0 / 0

05.02.2017, 18:20

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39398982

Person2713

Гость

Переписал запрос через рекурсию, правда есть одно но, не могу понять как выводить для каждого сотрудника в отдельной строчке руководителей. Например, есть иерархия /Петров/Иванов/Козлов/Петряев. В итоге для Петряева должны получить результат:
Сотрудник Руководитель
Петряев Козлов
Петряев Иванов
Петряев Петров

Подскажите пожалуйста какие есть пути решения.

...

Рейтинг:

0 / 0

05.02.2017, 23:16

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39398983

Person2713

Гость

Person2713Переписал запрос через рекурсию, правда есть одно но, не могу понять как выводить для каждого сотрудника в отдельной строчке руководителей. Например, есть иерархия /Петров/Иванов/Козлов/Петряев. В итоге для Петряева должны получить результат:
Сотрудник Руководитель
Петряев Козлов
Петряев Иванов
Петряев Петров

Подскажите пожалуйста какие есть пути решения.
А в целом должно быть так:
Сотрудник Руководитель
Иванов Петров
Козлов Иванов
Козлов Петров
Петряев Козлов
Петряев Иванов
Петряев Петров

...

Рейтинг:

0 / 0

05.02.2017, 23:18

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39399046

Vint

Участник

Откуда: Москва

Сообщения: 4 120

Рейтинг: 0 / 0

Person2713,
prior

dbms_photoshop,
мне кажется скоро можно будет давать ссылки на твою книгу вместо документации... по крайней мере не будет вопросов про русский язык))

...

Рейтинг:

0 / 0

06.02.2017, 09:56

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39399642

Person2713

Гость

Vint,
priop используется в иерархическом запросе. А у меня сейчас рекурсивный

...

Рейтинг:

0 / 0

06.02.2017, 20:29

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39399659

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

Person2713А в целом должно быть так

Код: 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.
37.
38.
39.
40.
41.

select  connect_by_root ename employee_name,
        case
          when connect_by_root ename != ename then ename
        end manager_name
  from  emp
  where connect_by_root ename != ename
     or mgr is null
  connect by empno = prior mgr
/
EMPLOYEE_N MANAGER_NA
---------- ----------
SMITH      FORD
SMITH      JONES
SMITH      KING
ALLEN      BLAKE
ALLEN      KING
WARD       BLAKE
WARD       KING
JONES      KING
MARTIN     BLAKE
MARTIN     KING
BLAKE      KING
CLARK      KING
SCOTT      JONES
SCOTT      KING
KING
TURNER     BLAKE
TURNER     KING
ADAMS      SCOTT
ADAMS      JONES
ADAMS      KING
JAMES      BLAKE
JAMES      KING
FORD       JONES
FORD       KING
MILLER     CLARK
MILLER     KING

26 rows selected.

SQL>

Или с индентацией:

Код: 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.
37.
38.
39.
40.
41.

select  connect_by_root ename employee_name,
        case
          when connect_by_root ename != ename then lpad(' ',(level - 2) * 2) || ename
        end manager_name
  from  emp
  where connect_by_root ename != ename
     or mgr is null
  connect by empno = prior mgr
/
EMPLOYEE_NAME   MANAGER_NAME
--------------- ---------------
SMITH           FORD
SMITH             JONES
SMITH               KING
ALLEN           BLAKE
ALLEN             KING
WARD            BLAKE
WARD              KING
JONES           KING
MARTIN          BLAKE
MARTIN            KING
BLAKE           KING
CLARK           KING
SCOTT           JONES
SCOTT             KING
KING
TURNER          BLAKE
TURNER            KING
ADAMS           SCOTT
ADAMS             JONES
ADAMS               KING
JAMES           BLAKE
JAMES             KING
FORD            JONES
FORD              KING
MILLER          CLARK
MILLER            KING

26 rows selected.

SQL>

SY.

...

Рейтинг:

0 / 0

06.02.2017, 21:04

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39399664

Person2713

Гость

SY,

Спасибо за примеры, но мне надо сделать иерархический запрос

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

select  e.employee_id,
        e.last_name || ' ' || e.first_name as employee_name,
        CONNECT_BY_ROOT e.employee_id as manager_id,
        CONNECT_BY_ROOT (e.last_name || ' ' || e.first_name) as manager_name,
        level - 2 as pathlen,
        sys_connect_by_path(e.last_name || ' ' || e.first_name, '/') as path,
        level
  from  employees e
  where level > 1
  connect by e.manager_id = prior(e.employee_id)
  order by e.employee_id,
           manager_id desc
;

c помощью рекурсии. Сделал вот часть:

Код: 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.

WITH
  reports_to_101 (emp_id, emp_name, mgr_id, mgr_name, ppath, lvl, pathlen) AS
  (
     SELECT e1.employee_id, 
            e1.last_name || ' ' || e1.first_name as emp_name, 
            e1.manager_id,
            null mgr_name,
            '/' || e1.last_name || ' ' || e1.first_name ppath,
            1 lvl,
            0 pathlen
     FROM employees e1
     WHERE e1.manager_id is NULL
   UNION ALL
     SELECT e.employee_id,
            e.last_name || ' ' || e.first_name as emp_name,
            e.manager_id,
            r.emp_name as mgr_name,
            ppath || '/' || e.last_name || ' ' || e.first_name,
            lvl + 1,
            r.lvl-1
     FROM reports_to_101 r, employees e
     WHERE r.emp_id = e.manager_id
  )
SELECT  emp_id, 
        emp_name, 
        mgr_id,
        mgr_name,
        ppath, 
        lvl,
        pathlen
FROM reports_to_101
where lvl > 0 
ORDER BY emp_id,
         mgr_id 
;

и вот не могу понять сотрудника выводить всех его начальников в отдельных строках

...

Рейтинг:

0 / 0

06.02.2017, 21:17

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39399668

dbms_photoshop

Участник

Откуда: sqlmdx.net

Сообщения: 5 140

Рейтинг: 0 / 0

Person2713и вот не могу понять сотрудника выводить всех его начальников в отдельных строкахЯ смотрю ты делаешь успехи и смог найти "Recursive Subquery Factoring: Examples" по ссылке, что я дал.
Теперь перейди ко второму примеру и увидь там "MGR_LIST". Неужели это так сложно?

Если так туго идет - лучше меняй специальность, потом потратишь пять лет, а работу найти невозможно.

...

Рейтинг:

0 / 0

06.02.2017, 21:43

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39399679

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

Person2713Спасибо за примеры, но мне надо c помощью рекурсии.

Код: 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.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.

with r(
       ename,
       mgr,
       lvl
      ) as (
             select  ename,
                     mgr,
                     1
               from  emp e1
            union all
             select  r.ename,
                     e.mgr,
                     r.lvl + 1
               from  r,
                     emp e
               where e.empno = r.mgr
                 and e.mgr is not null
           )
search depth first by lvl set level_order
select  ename employee_name,
        (select lpad(' ',(lvl - 1)  * 2) || e.ename from emp e where e.empno = r.mgr) manager_name
  from  r
  order by level_order
/
EMPLOYEE_NAME   MANAGER_NAME
--------------- ---------------
ADAMS           SCOTT
ADAMS             JONES
ADAMS               KING
ALLEN           BLAKE
ALLEN             KING
BLAKE           KING
CLARK           KING
FORD            JONES
FORD              KING
JAMES           BLAKE
JAMES             KING
JONES           KING
KING
MARTIN          BLAKE
MARTIN            KING
MILLER          CLARK
MILLER            KING
SCOTT           JONES
SCOTT             KING
SMITH           FORD
SMITH             JONES
SMITH               KING
TURNER          BLAKE
TURNER            KING
WARD            BLAKE
WARD              KING

26 rows selected.

SQL>

SY.

...

Рейтинг:

0 / 0

06.02.2017, 22:14

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39399758

Person2713

Гость

dbms_photoshop,
Каюсь поленился)

...

Рейтинг:

0 / 0

07.02.2017, 06:30

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39399762

Person2713

Гость

Person2713dbms_photoshop,
Каюсь поленился)

Второй пример не сильно помог, так как показывается как вывести целый путь. Но всё равно намёк понял буду документацию читать).
SY, спасибо за пример

...

Рейтинг:

0 / 0

07.02.2017, 06:58

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39405387

Person2713

Гость

Ребят, знаю что всем надоел. Но все-таки подскажите как решается вот эта задача:

Для каждого заказчика выбрать в виде строки через запятую даты его заказов. Для конкатенации дат заказов использовать sys_connect_by_path (иерархический запрос). Для отбора «последних» строк использовать connect_by_isleaf.

В данной задаче надо вывести информацию по заказчикам с помощью иерархического запроса, правда в самой таблице иерархии никакой нет. В oracle docs нашел пример, как вывести инфу об одном заказчике, а как для всех не могу понять.
Вот все что пока сделал:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

select  customer_id,
        ltrim(SYS_CONNECT_BY_PATH (order_date,', '),', ') as order_dates
  from  (
          select  rownum r,
                  order_date,
                  customer_id 
            from  orders 
            where customer_id = 101
        )
   where connect_by_isleaf = 1
   start with r = 1
   connect by r = prior r + 1
;

...

Рейтинг:

0 / 0

16.02.2017, 07:11

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39405396

Elic

Участник

Сообщения: 30 360

Рейтинг: 0 / 0

Person2713Для конкатенации дат заказов использовать sys_connect_by_path (иерархический запрос).Это устаревшее бессмысленно академическое баловство. RTFM LISTAGG
Person2713

Код: plsql

rownum r

Код: plsql

row_number() over (partition by customer_id order by order_date) as r

...

Рейтинг:

0 / 0

16.02.2017, 07:40

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39406068

Person2713

Гость

ElicPerson2713Для конкатенации дат заказов использовать sys_connect_by_path (иерархический запрос).Это устаревшее бессмысленно академическое баловство. RTFM LISTAGG
Person2713

Код: plsql

rownum r

Код: plsql

row_number() over (partition by customer_id order by order_date) as r

Elic спасибо за решение, но всё же мне нужен иерархический запрос, так как это одно из заданий лабы. А ещё лучше не решение а объяснение как это можно сделать. Не могу понять как надо мыслить, чтобы такие запросы писать.

...

Рейтинг:

0 / 0

16.02.2017, 18:35

| Ответить | Цитировать | Написать

Выполнение рекурсивного запроса с помощью не рекурсивных функций

#39406097

ora601

Участник

Сообщения: 785

Рейтинг: 0 / 0

Person2713
Для каждого заказчика выбрать в виде строки через запятую даты его заказов. Для конкатенации дат заказов использовать sys_connect_by_path (иерархический запрос). Для отбора «последних» строк использовать connect_by_isleaf.

А ещё лучше не решение а объяснение как это можно сделать.

На это можно дать только обьяснение почему так лучше не делать.

...

Рейтинг:

0 / 0

16.02.2017, 19:11

| Ответить | Цитировать | Написать

25 сообщений из 38, страница 1 из 2

все

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=52&msg=39399679&tid=1886392]:	0ms
get settings:	10ms
get forum list:	20ms
check forum access:	4ms
check topic access:	4ms
track hit:	52ms
get topic data:	13ms
get forum data:	4ms
get page messages:	80ms
get tp. blocked users:	1ms
others:	243ms

total:	431ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы