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

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');

使用 STARTWITH  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