oracle 10g递归查询 求高手帮忙解决 跪谢!!!

三、、、水 2013-01-29 04:09:13
建表语句如下(部门表,产品表):
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'
求高手帮忙解决,先谢谢了
...全文
689 5 点赞 打赏 收藏 举报
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
三、、、水 2013-01-30
这个问题已经通过其他方式解决了,谢谢各位了
  • 打赏
  • 举报
回复
ajune 2013-01-29
你的表建的不好吧,表dept里面DEPT_ID,PARENT_ID,DEPT_NAME,PRODUCT,然后再建一个表productinfo,字段为 PRODUCT_ID,PRODUCT_NAME ,这样表dept里的PRODUCT和表productinfo的PRODUCT_ID建立个外连,然后按你的要求只需要在select语句中使用左连就行了
  • 打赏
  • 举报
回复
善若止水 2013-01-29
楼主发的问题非常好,把表结构以及数据都写出来,省去了很多的麻烦。 我的方法比较笨,好在结果是符合你的要求的。你看看,有问题再说 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
  • 打赏
  • 举报
回复
三、、、水 2013-01-29
2楼大哥辛苦了! 但是这个sql好像不太满足我的要求啊,我要的查询结果是: 在查询到某个一级部门下所有的产品的情况下同时把产品所属部门全部查询出来 要把所属部门一层一层的列出来,部门最多有3级这个刚才忘说明了,抱歉 效果是这样的 1级部门 2级部门 3级部门 产品id 产品名称 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' 如果部门没有3级的话,其他的设为空
  • 打赏
  • 举报
回复
restbely 2013-01-29
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
  • 打赏
  • 举报
回复
相关推荐
发帖
Oracle
加入

1.6w+

社区成员

Oracle开发相关技术讨论
申请成为版主
帖子事件
创建了帖子
2013-01-29 04:09
社区公告
暂无公告