请教一个较难的sql的写法，类似于部门树，但一个部门可能有多个上级部门，请帮忙

dream_land

类似于以下数据：

1. SQL> select * from test order by no;
   
2. 
   
3. NO         PARENT_NO            MANAGER_ID NAME
   
4. ---------- -------------------- ---------- --------------------
   
5. 099                                      1 最高级部门
   
6. 100        099                           2 1部门1
   
7. 101        099                           8 1部门2
   
8. 102        099                           5 1部门3
   
9. 201        100                           3 2部门1
   
10. 301        201                           4 3部门1
    
11. 302        201                          10 3部门2
    
12. 401        301                           6 4部门1
    
13. 402        302                           7 4部门2
    
14. 403        302                           9 4部门3
    
15. 501        401,402                      11 多个上级部门1
    
16. 502        401                          13 一个上级部门
    
17. 503        401,402,403                  12 多个上级部门2

复制代码

no字段代表一个部门，parent_no是上级部门no，多个上级部门以逗号隔开，manager_id相当于部门负责人id，name是部门名称，现在不要再说表结构设计方面的问题。只考虑把各个部门和负责人id的映射关系查出来做成一个视图，上级部门的负责人id可以对应到他所在的部门以及所有下级部门。

我想到的视图查询语句如下：

1. 
   
2. SELECT distinct manager_id,
   
3.        FIRST_VALUE (name) OVER (PARTITION BY part ORDER BY lv) name,
   
4.        FIRST_VALUE (no) OVER (PARTITION BY part ORDER BY lv) no
   
5.   FROM (SELECT     LEVEL lv, ROWNUM - LEVEL part, a.*
   
6.               FROM test a
   
7.         CONNECT BY INSTR (PRIOR a.parent_no, a.no) > 0) b;
   
8. 
   

复制代码

这个结果不正确，究其原因，就是利用ROWNUM - LEVEL作为分组，不能把一个部门的所有上级作为一个分组。

不知道把问题说清楚没有，请各位帮我看看这个sql该怎么写，多谢！！

附建表语句和数据插入sql：

1. 
   
2. CREATE TABLE TEST
   
3. (
   
4.   NO          VARCHAR2(10 BYTE),
   
5.   PARENT_NO   VARCHAR2(100 BYTE),
   
6.   MANAGER_ID  NUMBER,
   
7.   NAME        VARCHAR2(50 BYTE)
   
8. );
   
9. 
   
10. 
    
11. INSERT INTO TEST ( NO, PARENT_NO, MANAGER_ID, NAME ) VALUES (
    
12. '302', '201', 10, '3部门2');
    
13. INSERT INTO TEST ( NO, PARENT_NO, MANAGER_ID, NAME ) VALUES (
    
14. '501', '401,402', 11, '多个上级部门1');
    
15. INSERT INTO TEST ( NO, PARENT_NO, MANAGER_ID, NAME ) VALUES (
    
16. '502', '401', 13, '一个上级部门');
    
17. INSERT INTO TEST ( NO, PARENT_NO, MANAGER_ID, NAME ) VALUES (
    
18. '503', '401,402,403', 12, '多个上级部门2');
    
19. INSERT INTO TEST ( NO, PARENT_NO, MANAGER_ID, NAME ) VALUES (
    
20. '099', NULL, 1, '最高级部门');
    
21. INSERT INTO TEST ( NO, PARENT_NO, MANAGER_ID, NAME ) VALUES (
    
22. '100', '099', 2, '1部门1');
    
23. INSERT INTO TEST ( NO, PARENT_NO, MANAGER_ID, NAME ) VALUES (
    
24. '201', '100', 3, '2部门1');
    
25. INSERT INTO TEST ( NO, PARENT_NO, MANAGER_ID, NAME ) VALUES (
    
26. '301', '201', 4, '3部门1');
    
27. INSERT INTO TEST ( NO, PARENT_NO, MANAGER_ID, NAME ) VALUES (
    
28. '102', '099', 5, '1部门3');
    
29. INSERT INTO TEST ( NO, PARENT_NO, MANAGER_ID, NAME ) VALUES (
    
30. '401', '301', 6, '4部门1');
    
31. INSERT INTO TEST ( NO, PARENT_NO, MANAGER_ID, NAME ) VALUES (
    
32. '402', '302', 7, '4部门2');
    
33. INSERT INTO TEST ( NO, PARENT_NO, MANAGER_ID, NAME ) VALUES (
    
34. '101', '099', 8, '1部门2');
    
35. INSERT INTO TEST ( NO, PARENT_NO, MANAGER_ID, NAME ) VALUES (
    
36. '403', '302', 9, '4部门3');
    
37. COMMIT;
    
38. 
    

复制代码

renxiao2003

这个好像可以实现。明天帮你看看。

duolanshizhe

有时候所得结果不太好描述时，建议文字+结果的方式给大家提供

比如你想要达到的效果，贴出，这样比较明确，文字有时候不太好理解

renxiao2003

select a.no as no,b.no as parent_no,a.manager_id manage_id,a.name name
from test a,test b
where b.no(+) = a.parent_no
order by a.no

这个关于多级部门的还没有取出来。等等 。

renxiao2003

1. column parent_no format A15;
   
2. column name format A30;
   
3. select distinct a.no as no,a.parent_no as parent_no ,a.manager_id manage_id,a.name name
   
4. from test a,test b
   
5. where instr(a.parent_no,b.no) > 0
   
6.      or a.parent_no is null
   
7. order by a.no;
   

复制代码

这个应该可以得到你的结果。

renxiao2003

这是运行结果。