oracle 10g递归查询 求高手帮忙解决 跪谢！！！
本帖最后由 chencjm126 于 2013-01-29 16:10:38 编辑 建表语句如下（部门表，产品表）：
create table dept(
DEPT_ID NUMBER(2) NOT NULL, --部门ID
PARENT_ID NUMBER(2) , --上级部门ID
DEPT_NAME VARCHAR2(10) --部门名称
);
insert into dept (DEPT_ID,PARENT_ID,DEPT_NAME) values (1,null,'山东1' );
insert into dept (DEPT_ID,PARENT_ID,DEPT_NAME) values (2,1 ,'山东1-2' );
insert into dept (DEPT_ID,PARENT_ID,DEPT_NAME) values (3,1 ,'山东1-3' );
insert into dept (DEPT_ID,PARENT_ID,DEPT_NAME) values (4,2 ,'山东1-2-4');
insert into dept (DEPT_ID,PARENT_ID,DEPT_NAME) values (5,2 ,'山东1-2-5');
insert into dept (DEPT_ID,PARENT_ID,DEPT_NAME) values (6,3 ,'山东1-3-6');
insert into dept (DEPT_ID,PARENT_ID,DEPT_NAME) values (7,3 ,'山东1-3-7');
insert into dept (DEPT_ID,PARENT_ID,DEPT_NAME) values (8,3 ,'山东1-3-8');
insert into dept (DEPT_ID,PARENT_ID,DEPT_NAME) values (9,null,'山西1' );
insert into dept (DEPT_ID,PARENT_ID,DEPT_NAME) values (10,9 ,'山西1-2' );
create table product(
PRODUCT_ID NUMBER(2) NOT NULL, --部门ID
DEPT_ID NUMBER(2) NOT NULL, --部门ID
NAME VARCHAR2(50) --产品名称
);
insert into product (PRODUCT_ID,DEPT_ID,NAME) values (1,6,'产品1');
insert into product (PRODUCT_ID,DEPT_ID,NAME) values (2,7,'产品2');
insert into product (PRODUCT_ID,DEPT_ID,NAME) values (3,8,'产品3');
insert into product (PRODUCT_ID,DEPT_ID,NAME) values (4,2,'产品4');
insert into product (PRODUCT_ID,DEPT_ID,NAME) values (5,10,'产品5');
commit;
想要达到的查询效果：
在查询到某个一级部门下所有的产品的情况下同时把产品所属部门全部查询出来，效果如下：
DEPT_NAME DEPT_NAME DEPT_NAME PRODUCT_ID NAME
'1' '1-3' '1-3-6' 1 '产品1'
'1' '1-3' '1-3-7' 2 '产品2'
'1' '1-3' '1-3-8' 3 '产品3'
'1' '1-2' 4 '产品4'
求高手帮忙解决，先谢谢了 oracle sql
[解决办法]
WITH dept AS (
SELECT '1' AS DEPT_ID,'' AS PARENT_ID,'山東1' AS DEPT_NAME FROM DUAL
UNION ALL
SELECT '2' AS DEPT_ID,'1' AS PARENT_ID,'山東1-2' AS DEPT_NAME FROM DUAL
UNION ALL
SELECT '3' AS DEPT_ID,'1' AS PARENT_ID,'山東1-3' AS DEPT_NAME FROM DUAL
UNION ALL
SELECT '4' AS DEPT_ID,'2' AS PARENT_ID,'山東1-2-4' AS DEPT_NAME FROM DUAL
UNION ALL
SELECT '5' AS DEPT_ID,'2' AS PARENT_ID,'山東1-2-5' AS DEPT_NAME FROM DUAL
UNION ALL
SELECT '6' AS DEPT_ID,'3' AS PARENT_ID,'山東1-3-6' AS DEPT_NAME FROM DUAL
UNION ALL
SELECT '7' AS DEPT_ID,'3' AS PARENT_ID,'山東1-3-7' AS DEPT_NAME FROM DUAL
UNION ALL
SELECT '8' AS DEPT_ID,'3' AS PARENT_ID,'山東1-3-8' AS DEPT_NAME FROM DUAL
UNION ALL
SELECT '9' AS DEPT_ID,'' AS PARENT_ID,'山西1' AS DEPT_NAME FROM DUAL
UNION ALL
SELECT '10' AS DEPT_ID,'9' AS PARENT_ID,'山西1-2' AS DEPT_NAME FROM DUAL
),product AS (
SELECT '1' AS PRODUCT_ID,'6' AS DEPT_ID,'産品1' AS NAME FROM DUAL
UNION ALL
SELECT '2' AS PRODUCT_ID,'7' AS DEPT_ID,'産品2' AS NAME FROM DUAL
UNION ALL
SELECT '3' AS PRODUCT_ID,'8' AS DEPT_ID,'産品3' AS NAME FROM DUAL
UNION ALL
SELECT '4' AS PRODUCT_ID,'2' AS DEPT_ID,'産品4' AS NAME FROM DUAL
UNION ALL
SELECT '5' AS PRODUCT_ID,'10' AS DEPT_ID,'産品5' AS NAME FROM DUAL
)
select tb.*,ltrim(SYS_CONNECT_BY_PATH(DEPT_NAME, '==>'), '==>') as str
from (select dept.DEPT_ID,PARENT_ID,DEPT_NAME,NAME from dept left join product on dept.DEPT_ID = product.DEPT_ID) tb
start with PARENT_ID is null
connect by prior DEPT_ID = PARENT_ID
[解决办法]
楼主发的问题非常好，把表结构以及数据都写出来，省去了很多的麻烦。
我的方法比较笨，好在结果是符合你的要求的。你看看，有问题再说
SELECT CASE
WHEN INSTR(K.RN, '-', -1, 2) > 0 THEN
SUBSTR(K.RN, 1, INSTR(K.RN, '-', -1, 2) - 1)
ELSE
NULL
END DEPT_NAME,
CASE
WHEN INSTR(K.RN, '-', -1, 1) > 0 THEN
SUBSTR(K.RN, 1, INSTR(K.RN, '-', -1, 1) - 1)
ELSE
NULL
END DEPT_NAME,
K.RN DEPT_NAME,
K.PRODUCT_ID,
K.NAME
FROM (SELECT M.RN, D.PRODUCT_ID, D.NAME
FROM (SELECT T.DEPT_ID,
T.PARENT_ID,
SUBSTR(SYS_CONNECT_BY_PATH(T.DEPT_ID, '-'), 2) RN
FROM DEPT T
START WITH T.PARENT_ID IS NULL
CONNECT BY PRIOR T.DEPT_ID = T.PARENT_ID) M,
PRODUCT D
WHERE M.DEPT_ID = D.DEPT_ID) K
ORDER BY K.PRODUCT_ID
