Рекурсивный SQL в Oracle для выделения данных только один раз в строке

У меня есть следующие данные в Oracle

    ITEM_CNT ID
    0        1
    1        1
    2        1
    3        1
    0        2
    2        2
    3        2
    0        3
    1        3
    2        3
    3        3
    4        3

и я хочу данные в формате ниже. Обратите внимание, что ITEM_CNT 0 принимается идентификатором 1, поэтому идентификатор 2 должен принимать следующий доступный номер, который равен 2. Аналогично ITEM_CNT 1 принимается идентификатором 3 и так далее.

Обратите внимание, что если item_cnt уже занят идентификатором, он не может использоваться другим идентификатором. Также всегда выбирайте наименьшее доступное значение ITEM_CNT.

    ITEM_CNT ID
    0        1
    2        2
    1        3

Кроме того, обратите внимание, что я пробовал ниже, но для каждой дополнительной строки мне нужно написать еще один рекурсивный код, и если кто-то может сделать рекурсивный код ниже, было бы здорово

        SELECT Min(m3.item_cnt) item_cnt,
           m3.id            id,
           m3.item          item
    FROM   my_fil_data m3
    WHERE  m3.item_cnt NOT IN (SELECT Min(m4.item_cnt)
                               FROM   my_fil_data m4
                               WHERE  m3.id > m4.id
                                      AND m4.item_cnt NOT IN (SELECT
                                          Min(m5.item_cnt)
                                                              FROM   my_fil_data m5
                                                              WHERE  m4.id > m5.id
                                                                     AND m5.item_cnt
                                                                         NOT IN
                                                             (SELECT
                                          Min(m6.item_cnt)
                                                              FROM   my_fil_data m6
                                                              WHERE  m5.id > m6.id
                                                              GROUP  BY m6.id)
                                                              GROUP  BY m5.id)
                               GROUP  BY m4.id)
    GROUP  BY id,
              item

sql oracle recursive-query
person psaraj12    schedule 08.04.2020    source источник
comment
Почему ID 2 не получает ITEM_CNT 1? И почему ID 3 получает 2 значения ITEM_CNT?   -  person Nick    schedule 08.04.2020
comment
потому что ID 2 не имеет ITEM_CNT 1   -  person psaraj12    schedule 08.04.2020
comment
Но в вашем вопросе говорится: «Примечание», поскольку ITEM_CNT 0 принимается идентификатором 1, поэтому идентификатор 2 должен принимать следующий доступный номер, равный 2.   -  person Nick    schedule 08.04.2020
comment
я отредактировал вопрос   -  person psaraj12    schedule 08.04.2020

Ответы (1)


arrow_upward
0
arrow_downward

Мне удалось получить ответ, используя функцию и коллекцию.

Сначала определите коллекцию на уровне схемы.

create type list1 is table of number;

Затем создайте функцию ниже

    CREATE OR replace FUNCTION Fn_get_recursive_xyz2(p_id NUMBER)
    RETURN NUMBER
    AS
      l_result NUMBER;
      TYPE list2
        IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
      listrec  LIST2;
      listrec1 LIST1 := List1();
      l_num    NUMBER;
      l_mm     NUMBER;
      l_mn     NUMBER;
      l_cnt    NUMBER;
    BEGIN
        l_num := p_id - 1;

        IF p_id > 2 THEN
          FOR rec IN 1..l_num LOOP
              SELECT Count(1)
              INTO   l_cnt
              FROM   my_fil_data
              WHERE  id = rec;

              IF l_cnt = 0 THEN
                CONTINUE;
              END IF;

              SELECT Min(m3.item_cnt) item_cnt
              INTO   l_mm
              FROM   my_fil_data m3
              WHERE  m3.id = rec
                     AND m3.item_cnt NOT IN (SELECT *
                                             FROM   TABLE(listrec1));

              listrec1.extend;

              Listrec1(listrec1.last) := l_mm;

              SELECT Min(m3.item_cnt) item_cnt
              INTO   l_mn
              FROM   my_fil_data m3
              WHERE  m3.id = rec
                     AND m3.item_cnt NOT IN (SELECT Min(m4.item_cnt) item_cnt
                                             FROM   my_fil_data m4
                                             WHERE  m3.id > m4.id
                                             --and m4.item_cnt 
                                             -- not in (select * from table(listrec)
                                             --)
                                             GROUP  BY m4.id)
              GROUP  BY m3.id;

              listrec1.extend;

              Listrec1(listrec1.last) := l_mn;
          END LOOP;
        ELSIF ( p_id = 2 ) THEN
          SELECT Count(1)
          INTO   l_cnt
          FROM   my_fil_data
          WHERE  id = 1;

          IF l_cnt <> 0 THEN
            SELECT Min(m3.item_cnt) item_cnt
            INTO   l_mn
            FROM   my_fil_data m3
            WHERE  m3.id = 1
                   AND m3.item_cnt NOT IN (SELECT Min(m4.item_cnt) item_cnt
                                           FROM   my_fil_data m4
                                           WHERE  m3.id > m4.id
                                           --and m4.item_cnt 
                                           -- not in (select * from table(listrec)
                                           --)
                                           GROUP  BY m4.id)
            GROUP  BY m3.id;

            listrec1.extend;

            Listrec1(listrec1.last) := l_mn;
          END IF;
        END IF;

        SELECT Min(m3.item_cnt) item_cnt
        INTO   l_result
        FROM   my_fil_data m3
        WHERE  m3.id = p_id
               AND m3.item_cnt NOT IN (SELECT *
                                       FROM   TABLE(listrec1));

        listrec1.DELETE;

        RETURN l_result;
    END fn_get_recursive_xyz2;

Затем вы можете вызвать функцию, чтобы получить желаемый результат, как показано ниже.

   WITH fl
         AS (SELECT DISTINCT id AS id
             FROM   my_fil_data)
    SELECT Fn_get_recursive_xyz2(id) ITEM_CNT,
           id
    FROM   fl
    ORDER  BY id;
person psaraj12    schedule 09.04.2020