oracle数据库如何处理树形结构数据的sql写法

Question

我用的是oracle10g
有一个这样结构的表
create table TEST
(
  ID       NUMBER not null,
  NAME     VARCHAR2(10),
  FATHERID NUMBER
)数据
insert into TEST (ID, NAME, FATHERID)
values (1, '中国', 0);
insert into TEST (ID, NAME, FATHERID)
values (2, '山东', 1);
insert into TEST (ID, NAME, FATHERID)
values (3, '济南', 2);
insert into TEST (ID, NAME, FATHERID)
values (4, '青岛', 2);
insert into TEST (ID, NAME, FATHERID)
values (5, '淄博', 2);
insert into TEST (ID, NAME, FATHERID)
values (6, '潍坊', 2);
insert into TEST (ID, NAME, FATHERID)
values (7, '菏泽', 2);
我想得到这样的结果
中国 山东 济南
中国 山东 青岛
中国 山东 淄博
中国 山东 潍坊
中国 山东 菏泽数据的层次不确定sql该怎么写啊，请大家帮忙想想

Answer 1

个人愚见哈： 
你的目的是显示出上面的格式来么？   如果是，那可能要做行列转换，可以上网查一下，很多例子。如果不是为了展现格式，而是为了找到 例如 菏泽的上一级 上上级分别是谁？  展现这样一个归属层次，那可以用下面的例子试试。with table_a as 
(
select 1 id ,'中國' names , 0 fatherid from dual
union
select 2 id ,'山東' names , 1 fatherid from dual
union
select 3 id ,'濟南' names , 2 fatherid from dual
union
select 4 id ,'青島' names , 2 fatherid from dual
union
select 5 id ,'淄博' names , 2 fatherid from dual
union
select 6 id ,'濰坊' names , 2 fatherid from dual
union
select 7 id ,'菏澤' names , 2 fatherid from dual
union
select 8 id ,'長清' names , 3 fatherid from dual
)select a.fatherid,a.names
from table_a a
start with a.fatherid = '0'
connect by PRIOR a.id = a.fatherid;

Answer 2

？？？？……
我那个只是个例子~~ 我没有建table，就只能手写几笔数据~   你直接把这句改成你的table名字和对应栏位就行了select a.fatherid,a.names
from table_a a
start with a.fatherid = '0'
connect by PRIOR a.id = a.fatherid;

Answer 3

楼主描述一下你的应用吧，是否可以使用级联查询来做，一定要通过SQL一次查询出所有的结果吗？

Answer 4

对于列数不确定的sql，最好用动态sql来做。

Answer 5

如果只是要求一个字段的话可以这样写
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.C:\Documents and Settings\Administrator>sqlplus / as sysdbaSQL*Plus: Release 10.1.0.2.0 - Production on 星期四 4月 7 16:41:15 2011Copyright (c) 1982, 2004, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining optionsSQL> create table TEST
  2  (
  3    ID NUMBER not null,
  4    NAME VARCHAR2(10),
  5    FATHERID NUMBER
  6  )
  7  ;Table created.SQL> insert into TEST (ID, NAME, FATHERID)
  2  values (1, '中国', 0);1 row created.SQL> insert into TEST (ID, NAME, FATHERID)
  2  values (2, '山东', 1);1 row created.SQL> insert into TEST (ID, NAME, FATHERID)
  2  values (3, '济南', 2);1 row created.SQL> insert into TEST (ID, NAME, FATHERID)
  2  values (4, '青岛', 2);1 row created.SQL> insert into TEST (ID, NAME, FATHERID)
  2  values (5, '淄博', 2);1 row created.SQL> insert into TEST (ID, NAME, FATHERID)
  2  values (6, '潍坊', 2);1 row created.SQL> insert into TEST (ID, NAME, FATHERID)
  2  values (7, '菏泽', 2);1 row created.SQL>
SQL> commit;Commit complete.SQL> select sys_connect_by_path(name,' ') from test t
  2  where connect_by_isleaf=1
  3  start with id=1
  4  connect by prior id=fatherid;SYS_CONNECT_BY_PATH(NAME,'')
-------------------------------------------------------------------------------- 中国 山东 济南
 中国 山东 青岛
 中国 山东 淄博
 中国 山东 潍坊
 中国 山东 菏泽SQL>

Answer 6

create table TEST
(
  ID NUMBER not null,
  NAME VARCHAR2(10),
  FATHERID NUMBER
)
insert into TEST (ID, NAME, FATHERID)
values (1, '中国', 0);
insert into TEST (ID, NAME, FATHERID)
values (2, '山东', 1);
insert into TEST (ID, NAME, FATHERID)
values (3, '济南', 2);
insert into TEST (ID, NAME, FATHERID)
values (4, '青岛', 2);
insert into TEST (ID, NAME, FATHERID)
values (5, '淄博', 2);
insert into TEST (ID, NAME, FATHERID)
values (6, '潍坊', 2);
insert into test (id, name, fatherid)
values (7, '菏泽', 2);
insert into test (id, name, fatherid)
values (8, '河北', 1);
insert into test (id, name, fatherid)
values (9, '石家庄', 8);
insert into test (id, name, fatherid)
values (10, '唐山', 8);
insert into test (id, name, fatherid)
values (11, '北京', 1);
insert into test (id, name, fatherid)
values (12, '北京', 11);
........................
select a. name1,a.name2,b.name2 name3 from
(
select id,name1,name2,fatherid from 
 (
 select connect_by_root(id) id, connect_by_root(name) name1,name name2,fatherid
  from test
 where level = 2 
 connect by prior id=fatherid 
 )where fatherid = 1
)a 
left join
(
 select id,name1,name2,fatherid from 
 (
 select connect_by_root(id) id, connect_by_root(name) name1,name name2,fatherid
  from test
 where level = 2 
 connect by prior id=fatherid 
  )where fatherid != 1
)b on a.name2 = b.name1动态实现

Answer 7

上面fatherid = 1 写死了， 忘记了  现补充如下：
select a. name1,a.name2,b.name2 name3 from
(
select id,name1,name2,fatherid from 
 (
 select connect_by_root(id) id, connect_by_root(name) name1,name name2,fatherid
  from test
 where level = 2 
 connect by prior id=fatherid 
 )where fatherid  = (select min(id) from test where fatherid = 0)
)a 
left join
(
 select id,name1,name2,fatherid from 
 (
 select connect_by_root(id) id, connect_by_root(name) name1,name name2,fatherid
  from test
 where level = 2 
 connect by prior id=fatherid 
  )where fatherid  != (select min(id) from test where fatherid = 0)
)b on a.name2 = b.name1

Answer 8

再补充。楼主看这个吧。
create table TEST
(
  ID NUMBER not null,
  NAME VARCHAR2(10),
  fatherid number
);
insert into TEST (ID, NAME, FATHERID)
values (1, '中国', 0);
insert into TEST (ID, NAME, FATHERID)
values (2, '山东', 1);
insert into TEST (ID, NAME, FATHERID)
values (3, '济南', 2);
insert into TEST (ID, NAME, FATHERID)
values (4, '青岛', 2);
insert into TEST (ID, NAME, FATHERID)
values (5, '淄博', 2);
insert into TEST (ID, NAME, FATHERID)
values (6, '潍坊', 2);
insert into test (id, name, fatherid)
values (7, '菏泽', 2);
insert into test (id, name, fatherid)
values (8, '河北', 1);
insert into test (id, name, fatherid)
values (9, '石家庄', 8);
insert into test (id, name, fatherid)
values (10, '唐山', 8);
insert into test (id, name, fatherid)
values (13, '日本', 0);
insert into test (id, name, fatherid)
values (14, '东京', 13);
insert into test (id, name, fatherid)
values (15, '东京北', 14);
..............
..............select a. name1,a.name2,b.name2 name3  from
(
select id,name1,name2,fatherid from 
 (
 select connect_by_root(id) id, connect_by_root(name) name1,name name2,fatherid
  from test
 --where level = 2 
 connect by prior id=fatherid 
 )where fatherid  in (select distinct  id from test where fatherid = 0)
)a 
left join
(
 select id,name1,name2,fatherid from 
 (
 select connect_by_root(id) id, connect_by_root(name) name1,name name2,fatherid
  from test
-- where level = 2 
 connect by prior id=fatherid 
  )where fatherid  not in  (select distinct id from test where fatherid = 0)
)b on a.name2 = b.name1 where a.name1 in  (select distinct name from test where fatherid = 0)执行SQL代码,你懂的