代码之家 › 专栏 › 技术社区 › dacracot

如何在Oracle中使用WITH而不是CONNECT BY来获得递归SQL的深度?

recursive-query oracle sql

dacracot · 技术社区 · 9 年前

CREATE TABLE tree
    (
    key NUMBER(5) NOT NULL PRIMARY KEY,
    name VARCHAR(15) NOT NULL,
    treeHier NUMBER(5)
    );

INSERT INTO tree VALUES('11','Software',NULL);
INSERT INTO tree VALUES('22','OS','11');
INSERT INTO tree VALUES('33','Linux','22');
INSERT INTO tree VALUES('44','Windows','22');
INSERT INTO tree VALUES('55','DB','11');
INSERT INTO tree VALUES('66','Oracle','55');
INSERT INTO tree VALUES('77','MS-SQL','55');

COMMIT;

SELECT
    LPAD(' ',LEVEL*2,' ')||' '||
    name||' '||
    key||' '||
    NVL(TO_CHAR(treeHier),'NULL')
FROM
    tree
START WITH
    key = 11
CONNECT BY
    treeHier = PRIOR key
ORDER BY
    key ASC;

   Software 11 NULL
     OS 22 11
       Linux 33 22
       Windoze 44 22
     DB 55 11
       Oracle 66 55
       MS-SQL 77 55

7 rows selected.

我认为,自11g以来,Oracle允许程序员使用WITH子句创建递归查询。

WITH myRecurse(key,name,treeHier) AS
    (
        SELECT
            tree.key, tree.name, tree.treeHier
        FROM
            tree
        WHERE
            tree.key = 11
    UNION ALL
        SELECT
            tree.key, tree.name, tree.treeHier
        FROM
            tree
        JOIN
            myRecur ON tree.treeHier= myRecurse.key
        ORDER BY
            3 DESC
    )
SELECT
    name||' '||
    key||' '||
    NVL(TO_CHAR(treeHier),'NULL')
FROM
    myRecurse;

Software 11 NULL
OS 22 11
DB 55 11
Linux 33 22
Windoze 44 22
Oracle 66 55
MS-SQL 77 55

7 rows selected.

有没有一种方法可以使用LEVEL或其他方法,这样我就可以使用WITH子句缩进并获得类似CONNECT BY,START WITH方法的输出?

1 回复 | 直到 9 年前

mathguy 9 年前

with r ( lvl, key, name, treehier ) as (
         select  1, key, name, treehier
           from  tree
           where treehier is null
         union all
         select  r.lvl + 1, t.key, t.name, r.key
         from    r join tree t on r.key = t.treehier
       )
       search depth first by name set ord
select   rpad(' ', 2 * (lvl - 1), ' ') || name as name, key, treehier
from     r
order by ord
;

NAME                                  KEY   TREEHIER
------------------------------ ---------- ----------
Software                               11           
  DB                                   55         11
    MS-SQL                             77         55
    Oracle                             66         55
  OS                                   22         11
    Linux                              33         22
    Windows                            44         22

然后,您可以在ORDER BY子句中的后面添加关键字DESC(用于降序排序,而不是默认的升序排序) ord (几乎肯定不是你想要的!)或者,从ORDER BY子句中删除DESC后,将其添加到 name