Question

oracle树型结构查询

有一个表：
ID　　　　　PARENTID　　　　　NAME
---------　　------------------　　--------------------------
1 　　　　　0 　　　　　　　　国内
2 　　　　　0 　　　　　　　　国外
3 　　　　　1 　　　　　　　　广东省
4 　　　　　1 　　　　　　　　陕西省
5 　　　　　1 　　　　　　　　黑龙江省
6 　　　　　3 　　　　　　　　广州市
7 　　　　　4 　　　　　　　　西安市
8 　　　　　5 　　　　　　　　大庆市
9 　　　　　7 　　　　　　　　咸阳市
10 　　　　 6 　　　　　　　　广州市
11　　　　　9　　　　　　　　秦都区
12 　　　　 10 　　　　　　　　荔湾区

如何查询，能得到如下结构：
ID01　　　 ID02　　　 ID03　　　 ID04　　　 ID05　　　　NAME
-----------　　-----------　　-----------　　-----------　　-----------　　-----------　　
1　　　　　　　　　　　　　　　　　　　　　　　　　　　国内
2　　　　　　　　　　　　　　　　　　　　　　　　　　　国外
1　　　　　3 　　　　　　　　　　　　　　　　　　　　　广东省
1　　　　　4 　　　　　　　　　　　　　　　　　　　　　陕西省
1　　　　　5 　　　　　　　　　　　　　　　　　　　　　黑龙江省
1　　　　　3 　　　　　6 　　　　　　　　　　　　　　　广州市
1　　　　　4　　　　　7 　　　　　　　　　　　　　　　西安市
1　　　　　5　　　　　8 　　　　　　　　　　　　　　　大庆市
1　　　　　4　　　　　7 　　　　　9　　　　　　　　　　咸阳市
1　　　　　3　　　　　6　　　　　10　　　　　　　　　　广州市
1　　　　　4　　　　　7 　　　　　9　　　　　11　　　　秦都区
1　　　　　3　　　　　6　　　　　10　　　　　12　　　　秦都区
最后那行写错，是荔湾区

建表SQL，不知道这样行不行：
select 1 ID,0 PARENTID,'国内' NAME from dual
union all
select 2,0,'国外' from dual
union all
select 3,1,'广东省' from dual
union all
select 4,1,'陕西省' from dual
union all
select 5,1,'黑龙江省' from dual
union all
select 6,3,'广州市' from dual
union all
select 7,4,'西安市' from dual
union all
select 8,5,'大庆市' from dual
union all
select 9,7,'咸阳市' from dual
union all
select 10,6,'广州市' from dual
union all
select 11,9,'秦都区' from dual
union all
select 12,10,'荔湾区' from dual

Answer 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, NULL, '.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, NULL, '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, NULL, 'Dat abase'); INSERT INTO test_tree VALUES(11, 10, 'DB2'); INSERT INTO test_tree VALUES(12, 10, 'MySQ L'); INSERT INTO test_tree VALUES(13, 10, 'Oracl e'); INSERT INTO test_tree VALUES(14, 10, 'SQL S erver');

INSERT INTO test_tree VALUES(15, 13, 'PL/SQ L'); INSERT INTO test_tree VALUES(16, 15, 'Functi on'); INSERT INTO test_tree VALUES(17, 15, 'Proce dure'); INSERT INTO test_tree VALUES(18, 15, 'Packa ge'); INSERT INTO test_tree VALUES(19, 15, 'Curso r');

INSERT INTO test_tree VALUES(20, 14, 'T-SQL' );

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

SQL> ed Wrote file afiedt.buf

1 SELECT 2 LPAD(' ', 2*(LEVEL-1)) || test_val AS test_va l 3 FROM 4 test_tree 5 START WITH 6 test_id IN (1, 6, 10) 7* CONNECT BY PRIOR test_id = pid SQL> /

TEST_VAL -----------------------------------------------------------

.NET C# J# ASP.NET VB.NET J2EE EJB Servlet JSP Database DB2

TEST_VAL -----------------------------------------------------------

MySQL Oracle PL/SQL Function Procedure Package Cursor SQL Server T-SQL
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, NULL, '.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, NULL, '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, NULL, 'Dat abase'); INSERT INTO test_tree VALUES(11, 10, 'DB2'); INSERT INTO test_tree VALUES(12, 10, 'MySQ L'); INSERT INTO test_tree VALUES(13, 10, 'Oracl e'); INSERT INTO test_tree VALUES(14, 10, 'SQL S erver');

INSERT INTO test_tree VALUES(15, 13, 'PL/SQ L'); INSERT INTO test_tree VALUES(16, 15, 'Functi on'); INSERT INTO test_tree VALUES(17, 15, 'Proce dure'); INSERT INTO test_tree VALUES(18, 15, 'Packa ge'); INSERT INTO test_tree VALUES(19, 15, 'Curso r');

INSERT INTO test_tree VALUES(20, 14, 'T-SQL' );

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

SQL> ed Wrote file afiedt.buf

1 SELECT 2 LPAD(' ', 2*(LEVEL-1)) || test_val AS test_va l 3 FROM 4 test_tree 5 START WITH 6 test_id IN (1, 6, 10) 7* CONNECT BY PRIOR test_id = pid SQL> /

TEST_VAL -----------------------------------------------------------

.NET C# J# ASP.NET VB.NET J2EE EJB Servlet JSP Database DB2

TEST_VAL -----------------------------------------------------------

MySQL Oracle PL/SQL Function Procedure Package Cursor SQL Server T-SQL

20 rows selected.
20 rows selected

追问

SB

追答

DB

2B

Answer 2

这个难度够大的，尝试下，不过需要时间。
你的列是固定的吗。还是要根据表数据来自动决定

能不能把建表语句及数据的SQL都拿出来，省得自己打了。

追问

我这里级数不固定。不过可以按照5级来写

追答

能不能把建表语句及数据的SQL都拿出来，省得自己打了。

 

SELECT  CONNECT_BY_ROOT(ID) ID01, case  when level>= 2 then to_char(ID) else '' end ID02, case  when  level>= 3 then to_char(ID) else '' end ID03,
case  when  level>=4 then to_char(ID) else '' end ID04, case  when  level>=5 then to_char(ID) else '' end ID05, Name,   ID, PARENT_ID,   LEVEL,   CONNECT_BY_ISLEAF
FROM     t_citys
         START WITH PARENT_ID=0
         CONNECT BY PRIOR ID=PARENT_ID
         ORDER BY ID;