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