запрос иерархии (соединение по предложению)

Я пытаюсь написать запрос, используя предложение connect by, но я не могу с этим справиться.

Обычно у меня есть таблица:

CREATE TABLE "TESTOWA" (
    "ACCOUNT" VARCHAR2(20 BYTE), 
    "PARENT"  VARCHAR2(20 BYTE), 
    "PAYMENT" VARCHAR2(20 BYTE)
);

Insert into TESTOWA (ACCOUNT,PARENT,PAYMENT) values ('5436','5436','1');
Insert into TESTOWA (ACCOUNT,PARENT,PAYMENT) values ('4576','3457',null);
Insert into TESTOWA (ACCOUNT,PARENT,PAYMENT) values ('5763','5686','1');
Insert into TESTOWA (ACCOUNT,PARENT,PAYMENT) values ('5686','5686',null);
Insert into TESTOWA (ACCOUNT,PARENT,PAYMENT) values ('3457','5686',null);

И теперь то, что я хочу сделать, это найти учетные записи, столбец оплаты которых не заполнен (даже родительская учетная запись). Каждая учетная запись может иметь родительскую учетную запись (родительский столбец), которая указывает на идентификатор другой учетной записи. Может быть, будет проще, если я представлю это на примере:

 ACCOUNTID | PARENT | PAYMENT
-----------------------------
    5436   |  5436  |    1
    4576   |  3457  |  NULL
    5763   |  5643  |    1
    5686   |  5686  |    1
    3457   |  5686  |  NULL

Первая учетная запись в порядке - колонка оплаты заполнена. Второй не в порядке, потому что он нулевой, но, как мы видим, есть родительская учетная запись, поэтому теперь мы проверяем (3457 учетная запись), и снова столбец оплаты равен нулю, но снова есть родительская учетная запись (5686) и, наконец, есть столбец оплаты заполненный. Таким образом, для описанных выше ситуаций select не должен ничего представлять. Что, если бы таблица выглядела так:

 ACCOUNTID | PARENT | PAYMENT
------------------------------
    5436   |  5436  |    1
    4576   |  3457  |  NULL
    5763   |  5643  |    1
    5686   |  5686  |  NULL
    3457   |  5686  |  NULL

Как мы видим, единственное изменение равно null рядом с идентификатором учетной записи 5686, поэтому правильный выбор должен отображать учетные записи: 4576, 3457, 5686


sql oracle hierarchical-data connect-by
person maciek2791    schedule 09.11.2015    source источник

Ответы (1)


arrow_upward
1
arrow_downward

скрипт SQL

Настройка схемы Oracle 11g R2:

CREATE TABLE "TESTOWA" (   
  ACCOUNT NUMBER(4,0), 
  PARENT  NUMBER(4,0), 
  PAYMENT NUMBER(1,0)
);
Insert into TESTOWA values (5436,5436,1);
Insert into TESTOWA values (5686,5686,null);
Insert into TESTOWA values (5763,5686,1);
Insert into TESTOWA values (3457,5686,1); 
Insert into TESTOWA values (4576,3457,null);

Запрос 1:

SELECT t.*,
       CONNECT_BY_ROOT( PAYMENT ) AS HAS_PAYED
FROM   TESTOWA t
START WITH
       ACCOUNT = PARENT
OR     PAYMENT = 1
CONNECT BY
       NOCYCLE
       PRIOR ACCOUNT = PARENT
AND    PAYMENT IS NULL

Результаты:

| ACCOUNT | PARENT | PAYMENT | HAS_PAYED |
|---------|--------|---------|-----------|
|    5436 |   5436 |       1 |         1 |
|    3457 |   5686 |       1 |         1 |
|    4576 |   3457 |  (null) |         1 |
|    5686 |   5686 |  (null) |    (null) |
|    5763 |   5686 |       1 |         1 |
person MT0    schedule 09.11.2015
comment
МОЙ БОГ! Я бы никогда не догадался, как это сделать‹ Мне нужно это проанализировать :). Работает отлично ;) Спасибо! - person maciek2791; 09.11.2015
comment
START WITH ... PAYMENT = 1 гарантирует, что все платные учетные записи находятся в корне иерархии, а CONNECT BY ... PAYMENT IS NULL гарантирует, что платные учетные записи не будут включены в другие иерархии (так что вам не придется беспокоиться о двойном учете). Другие биты START WITH и CONNECT BY просто включают другие (неоплачиваемые) аккаунты в выборку. Поскольку каждая платная учетная запись всегда находится в корне иерархии, CONNECT_BY_ROOT( PAYMENT ) сообщит вам, заплатили вы или нет. - person MT0; 09.11.2015
comment
Хорошо, теперь я понял :) Еще раз спасибо! :) - person maciek2791; 09.11.2015