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

Question

现有类别表如下：
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          活动扳手         工属具类别\吊索类\扳手\活动扳手请高人指点！如遇正解不差分！

Answer 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 

Answer 2

 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
>吊索类
>吊索类>扳手
>吊索类>扳手>活动扳手
>工属具类别*/

Answer 3

有点罗嗦了，我等会优化下:
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>