Oracle中的递归SQL每行只分配一次数据 [英] Recursive SQL in Oracle to allocate data only once per row

问题描述

我在 Oracle 中有以下数据

I have the below data in 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 被 ID 1 占用，因此 ID 2 应该占用下一个可用数字，即 2.同样，ITEM_CNT 1 被 ID 3 占用，依此类推.

and I want the data in the below format. Note since ITEM_CNT 0 is taken by ID 1 hence ID 2 should take the next available number which is 2.Similarly ITEM_CNT 1 is taken by ID 3 and so on.

注意如果 item_cnt 已经被一个 ID 占用，它就不能被另一个 ID 使用.也总是选择最少的 ITEM_CNT 可用.

Note if an item_cnt is already taken by an ID it cannot be used by another ID. Also always choose the least ITEM_CNT available.

    ITEM_CNT ID
    0        1
    2        2
    1        3

另外，请注意，我尝试了下面的操作，但是对于每一行，我必须再编写一个递归代码，如果有人可以使下面的代码递归，那就太好了

Also, note I tried the below but for each additional row I have to write one more recursive code and if someone can make the below code recursive then it would be great

        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 

推荐答案

我能够使用函数和集合获得答案.

I was able to achieve the answer using a function and a collection.

首先，在架构级别定义一个集合.

First, define a collection at the schema level.

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; 

然后您可以调用该函数以获得如下所示的所需结果

Then you can call the function to get the desired result like below

   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; 

这篇关于Oracle中的递归SQL每行只分配一次数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！