oracle一条语句递归查询父子关系

2019独角兽企业重金招聘Python工程师标准>>>

1  建表：

CREATE TABLE test_tree (
  test_id   INT  NOT NULL,
  pid       INT,
  test_val  VARCHAR(10),
  PRIMARY KEY (test_id)
);
INSERT INTO test_tree VALUES(1, 0,   '.NET');
INSERT INTO test_tree VALUES(2, 1,      'C#');
INSERT INTO test_tree VALUES(3, 1,      'J#');
INSERT INTO test_tree VALUES(4, 1,      'ASP.NET');
INSERT INTO test_tree VALUES(5, 1,      'VB.NET');
INSERT INTO test_tree VALUES(6, 0,   'J2EE');
INSERT INTO test_tree VALUES(7, 6,      'EJB');
INSERT INTO test_tree VALUES(8, 6,      'Servlet');
INSERT INTO test_tree VALUES(9, 6,      'JSP');
INSERT INTO test_tree VALUES(10, 0,  'Database');
INSERT INTO test_tree VALUES(11, 10,    'DB2');
INSERT INTO test_tree VALUES(12, 10,    'MySQL');
INSERT INTO test_tree VALUES(13, 10,    'Oracle');
INSERT INTO test_tree VALUES(14, 10,    'SQL Server');
INSERT INTO test_tree VALUES(15, 13,    'PL/SQL');
INSERT INTO test_tree VALUES(16, 15,    'Function');
INSERT INTO test_tree VALUES(17, 15,    'Procedure');
INSERT INTO test_tree VALUES(18, 15,    'Package');
INSERT INTO test_tree VALUES(19, 15,    'Cursor');
INSERT INTO test_tree VALUES(20, 14,    'T-SQL');

使用 START WITH  CONNECT BY 语句实现树状查询

使用 SYS_CONNECT_BY_PATH  函数，获取节点的全路径.

COLUMN "FullPath" 

2:查询语句

SELECT
  LEVEL,
  test_id,
  test_val,
  SYS_CONNECT_BY_PATH(test_val, '\') AS "FullPath"
FROM
  test_tree
START WITH
  pid =0
CONNECT BY PRIOR test_id = pid
ORDER SIBLINGS BY test_val;

结果：

     LEVEL    TEST_ID TEST_VAL             FullPath
---------- ---------- -------------------- -----------------------------------
         1          1 .NET                 \.NET
         2          4 ASP.NET              \.NET\ASP.NET
         2          2 C#                   \.NET\C#
         2          3 J#                   \.NET\J#
         2          5 VB.NET               \.NET\VB.NET
         1         10 Database             \Database
         2         11 DB2                  \Database\DB2
         2         12 MySQL                \Database\MySQL
         2         13 Oracle               \Database\Oracle
         3         15 PL/SQL               \Database\Oracle\PL/SQL
         4         19 Cursor               \Database\Oracle\PL/SQL\Cursor
     LEVEL    TEST_ID TEST_VAL             FullPath
---------- ---------- -------------------- -----------------------------------
         4         16 Function             \Database\Oracle\PL/SQL\Function
         4         18 Package              \Database\Oracle\PL/SQL\Package
         4         17 Procedure            \Database\Oracle\PL/SQL\Procedure
         2         14 SQL Server           \Database\SQL Server
         3         20 T-SQL                \Database\SQL Server\T-SQL
         1          6 J2EE                 \J2EE
         2          7 EJB                  \J2EE\EJB
         2          9 JSP                  \J2EE\JSP
         2          8 Servlet              \J2EE\Servlet

转载于:https://my.oschina.net/u/2365087/blog/690052