代码之家 › 专栏 › 技术社区 › Jorge Campos harsha kumar Reddy

分层导入单一查询解决方案-Oracle

connect-by hierarchical-data select oracle sql

Jorge Campos harsha kumar Reddy · 技术社区 · 11 年前

我有一个有趣的问题,至少我是这么认为的。所以,我有一个具有上述结构的表(codes_table)(它是一个树菜单) 树是由两个整数加上一个点模式(总是)定义的,如下所示:

COD_MENU               NAME
01.                 Biggest Node
01.01.              Sun of 01.
01.01.01.           Sun of 01.01.
01.01.01.01.        Sun of 01.01.01.
01.01.01.02.        Sun of 01.01.01.
01.01.01.03.        Sun of 01.01.01.
01.01.01.04.        Sun of 01.01.01.
01.01.01.05.        Sun of 01.01.01.
01.02.              Sun of 01.
01.03.              Sun of 01.
01.03.01.           Sun of 01.03.
(etc...)

我需要的是生成插入脚本到一个具有数字结构的新表将实际的COD_MENU作为名称放在像这样的新结构:

ID     FATHER_ID         NAME 
1        NULL            01.
2        1               01.01.
3        2               01.01.01.
4        3               01.01.01.01.
5        3               01.01.01.02.
6        3               01.01.01.03.
7        3               01.01.01.04.
8        3               01.01.01.05.
9        1               01.02.
10       1               01.03.
11       10              01.03.01.

我已经在一个带有递归过程的plsql块代码中做到了这一点。以上是我的真实代码供参考。

declare
   auxId integer;

   procedure findNodes( pCod varchar2, pCurrId in out integer, pFatherId in integer  ) is
        ctHasSuns integer; 
        father    varchar2(20);
        idFhtTmp     integer;
     begin

        idFhtTmp  := pFatherId;

        if idFhtTmp is null then
            father := 'null';
        else
            father := idFhtTmp;
        end if;
        ctHasSuns := 0;

        SELECT count(cod_menu) into ctHasSuns FROM codes_table where SUBSTR (cod_menu, 1,LENGTH (cod_menu) - 3) = pCod and rownum < 10;

        if (ctHasSuns > 0 or pCurrId = 1) then
            dbms_output.put_line ('insert into newtable ( id, idfather, menu ) values ( '||pCurrId||','||father||', '''||pCod||''' );');
            idFhtTmp := pCurrId;
            for cHasSuns in ( SELECT cod_menu FROM codes_table where SUBSTR (cod_menu, 1,LENGTH (cod_menu) - 3) = pCod and rownum < 10 order by cod_menu) loop
                pCurrId := pCurrId + 1;
                findNodes( cHasSuns.cod_menu, pCurrId, idFhtTmp );
            end loop;
        else
            dbms_output.put_line ('insert into newtable ( id, idfather, menu ) values ( '||pCurrId||','||father||', '''||pCod||''' );');
        end if;

     end;

begin
  auxId := 1;
  findNodes( '01.', auxId, null );
end;

我想做的是在一个使用CONNECT BY/START WITH/ROWNUM和 LEVEL语句和usingn在必要时与同一个表联接。我尝试了很多事情,但都做不到提出了一个解决方案。这只是出于好奇。

我能做的最接近的事情是上面的选择,但我只有父亲的ID,而不是太阳的ID

WITH q AS (
select rownum id, father  from (
select a.noh father, count(*) 
from (select cod_menu noh, substr( cod_menu, 1, length(cod_menu)-3) as nofather from codes_table) a,
     (select cod_menu noh, substr( cod_menu, 1, length(cod_menu)-3) as nofather from codes_table) b
where a.noh = b.nofather
group by a.noh
having count(*) > 0 
order by a.noh)
), 
b as (
 SELECT cod_menu filho, father
  FROM (select cod_menu, substr( cod_menu, 1, length(cod_menu)-3) as father from codes_table)
  START WITH father is null
CONNECT BY prior cod_menu = father 
  ORDER BY cod_menu
)
SELECT * FROM q, b where q.father = b.father;

感谢您的时间和帮助。

1 回复 | 直到 11 年前

Przemyslaw Kruglej 11 年前

尝试以下解决方案:

CREATE TABLE codes_table (
  cod_menu VARCHAR2(40),
  name VARCHAR2(40)
);

INSERT INTO codes_table VALUES ('01.', 'Biggest Node');
INSERT INTO codes_table VALUES ('01.01.', 'Sun of 01.');
INSERT INTO codes_table VALUES ('01.01.01.', 'Sun of 01.01.');
INSERT INTO codes_table VALUES ('01.01.01.01.', 'Sun of 01.01.01.');
INSERT INTO codes_table VALUES ('01.01.01.02.', 'Sun of 01.01.01.');
INSERT INTO codes_table VALUES ('01.01.01.03.', 'Sun of 01.01.01.');
INSERT INTO codes_table VALUES ('01.01.01.04.', 'Sun of 01.01.01.');
INSERT INTO codes_table VALUES ('01.01.01.05.', 'Sun of 01.01.01.');
INSERT INTO codes_table VALUES ('01.02.', 'Sun of 01.');
INSERT INTO codes_table VALUES ('01.03.', 'Sun of 01.');
INSERT INTO codes_table VALUES ('01.03.01.', 'Sun of 01.03.');

COMMIT;

SELECT
    id,
    PRIOR id AS father_id,
    cod_menu AS name
  FROM (
    SELECT
      cod_menu,
      name,
      row_number() OVER (ORDER BY cod_menu) AS id
    FROM
      codes_table
)
START WITH cod_menu = '01.'
CONNECT BY SUBSTR(cod_menu, 1, LENGTH(cod_menu) - 3) = PRIOR cod_menu
;

检查SQLFiddle: Fiddle with it