ORACE 查询树结构数据

1. 表结构：CODE NAME C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 ILEVEL

2. 组成CODE NAME PARENTCODE

3. SQL语句

WITH 
tree_data AS
(
	SELECT
		CODE,
		NAME,
		'0' AS PARENTCODE,
		ILEVEL 
	FROM
		table 
	WHERE
		ILEVEL = 0
	UNION ALL
	SELECT
		CODE,
		NAME,
		NVL(SUBSTR( CODE, 1, INSTR( CODE, '.', 1, ILEVEL ) - 1 ),0 ) AS PARENTCODE,
		ILEVEL 
	FROM
		table
	WHERE
		ILEVEL > 0
)

SELECT
	CODE,
	NAME,
	PARENTCODE 
FROM
	tree_data START WITH ILEVEL = 0 CONNECT BY PRIOR CODE = PARENTCODE

4. (START WITH ILEVEL = 0) 从根目录查询，可追加条件