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