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