oracle中对树形结构的查询，我想得到一列显示起始节点到当前节点的完整路径，如何实现？

[I_am_Z]

现有类别表如下：
create table TBC_TM_CATEGORY (
CATEGORY_ID NVARCHAR2(50) not null,
SUPERIOR_ID NVARCHAR2(50) not null,
CATEGORY_NAME NVARCHAR2(50) not null,
constraint PK_TBC_TM_CATEGORY primary key (CATEGORY_ID)
)
数据如下：
Insert into TBC_TM_CATEGORY
(CATEGORY_ID, SUPERIOR_ID, CATEGORY_NAME)
Values
('191', '0', '吊索类');
Insert into TBC_TM_CATEGORY
(CATEGORY_ID, SUPERIOR_ID, CATEGORY_NAME)
Values
('0', '0', '工属具类别');
Insert into TBC_TM_CATEGORY
(CATEGORY_ID, SUPERIOR_ID, CATEGORY_NAME)
Values
('791', '191', '扳手');

Insert into TBC_TM_CATEGORY
(CATEGORY_ID, SUPERIOR_ID, CATEGORY_NAME)
Values
('1896', '791', '活动扳手');
希望得到的查询结果如下：
CATEGORY_ID, SUPERIOR_ID, CATEGORY_NAME, path
191 0 吊索类 工属具类别\吊索类
0 0 工属具类别 工属具类别
791 191 扳手 工属具类别\吊索类\扳手
1896 791 活动扳手 工属具类别\吊索类\扳手\活动扳手

请高人指点！如遇正解不差分！

[又是违规昵称]

占楼先

[zcs_1]

数据修改1,
update TBC_TM_CATEGORY set superior_id=' ' where category_id = '0';

category_id='0'对应的SUPERIOR_ID不能为'0'，否则逻辑上混乱，请楼主思考一下：

SQL语句如下：

select
CATEGORY_ID , SUPERIOR_ID , CATEGORY_NAME,
SYS_CONNECT_BY_PATH(CATEGORY_NAME,'\') path
from TBC_TM_CATEGORY
start with superior_id=' '
connect by prior category_id=superior_id

[oraclelogan]

[Quote=引用 2 楼 zcs_1 的回复:]
数据修改1,
update TBC_TM_CATEGORY set superior_id=' ' where category_id = '0';

category_id='0'对应的SUPERIOR_ID不能为'0'，否则逻辑上混乱，请楼主思考一下：

SQL语句如下：

select
CATEGORY_ID , SUPERIOR_ID , CATEGORY_NAME,
SYS_CONNECT_BY_PATH(CATEGORY_NAME,'\') path
from TBC_TM_CATEGORY
start with superior_id=' '
connect by prior category_id=superior_id
[/Quote]
学习了，先！

[I_am_Z]

非常感谢2楼兄弟的指正，您的解答非常棒！谢谢。结贴！

[jinjazz]

 update TBC_TM_CATEGORY set category_id=1 where category_id=0;
 
 select SYS_CONNECT_BY_PATH(category_name,'>') from TBC_TM_CATEGORY
 start with superior_id=0
 connect by  prior  category_id=superior_id　
 
/*
SYS_CONNECT_BY_PATH(CATEGORY_N
>吊索类
>吊索类>扳手
>吊索类>扳手>活动扳手
>工属具类别*/

[又是违规昵称]

有点罗嗦了，我等会优化下:

SQL> select C.CATEGORY_ID, C.SUPERIOR_ID, C.CATEGORY_NAME, C.PATH from
  2  (
  3    select * from
  4    (
  5      select level lv, CATEGORY_ID, SUPERIOR_ID, CATEGORY_NAME,
  6             ltrim(sys_connect_by_path(CATEGORY_NAME,'/') , '/') PATH from TBC_TM_CATEGORY
  7      connect by nocycle  prior SUPERIOR_ID = CATEGORY_ID
  8    ) B
  9    where (CATEGORY_ID,lv) in
 10    (
 11      select A.CATEGORY_ID,max(A.lv) from (
 12      select level lv, CATEGORY_ID, SUPERIOR_ID, CATEGORY_NAME,
 13      ltrim(sys_connect_by_path(CATEGORY_NAME,'/') , '/') from TBC_TM_CATEGORY
 14      connect by nocycle  prior SUPERIOR_ID = CATEGORY_ID
 15    )A
 16    group by A.CATEGORY_ID
 17    )
 18  ) C
 19  ;
 
CATEGORY_ID                                        SUPERIOR_ID                                        CATEGORY_NAME                                      PATH
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------------------------------------
0                                                  0                                                  工属具类别                                         工属具类别
1896                                               791                                                活动扳手                                           活动扳手
791                                                191                                                扳手                                               活动扳手/扳手
191                                                0                                                  吊索类                                             活动扳手/扳手/吊索类
 
SQL> 

[I_am_Z]

非常感谢1楼的赐教！

[ypopo1234]

6楼朋友在哪都能看到你啊