请问如何查出树形结构的某个节点的所有子孙节点？

Question

本帖最后由 sysuscnu 于 2010-10-27 16:06:48 编辑

Answer 1

SQL> select * from t_org;
 
ORGANIZE_ID TOP_ORGANIZE_ID
----------- ---------------
          1 
          2               1
          3               1
          4               2
          5               4
          6               2
 
6 rows selected
 
SQL> 
SQL> select ORGANIZE_ID, max(son_organize_id)
  2    from (select ORGANIZE_ID,
  3                 wm_concat(son_organize_id) over(partition by ORGANIZE_ID order by son_organize_id asc) son_organize_id
  4            from (select connect_by_root(ORGANIZE_ID) ORGANIZE_ID,
  5                         t.organize_id son_organize_id
  6                    from t_org t
  7                  connect by prior ORGANIZE_ID = t.top_ORGANIZE_ID))
  8   group by ORGANIZE_ID
  9  ;
 
ORGANIZE_ID MAX(SON_ORGANIZE_ID)
----------- --------------------------------------------------------------------------------
          1 1,2,3,4,5,6
          2 2,4,5,6
          3 3
          4 4,5
          5 5
          6 6
 
6 rows selected
 
SQL> 

Answer 2

SQL> ed
已写入 file afiedt.buf  1  select fid,wm_concat(cid) cid
  2  from
  3    (select connect_by_root(oid) fid,
  4    t.oid cid
  5    from tb t
  6    connect by prior oid = t.tid)
  7* group by fid
SQL> /       FID CID
---------- --------------------
         1 1,2,4,6,3,5
         2 2,4,5,6
         3 3
         4 4,5
         5 5
         6 6

Answer 3

WITH t_sys_organize AS(
SELECT '1'organize_id, NULL top_organize_id  FROM dual UNION ALL
SELECT '2', '1' FROM dual UNION ALL
SELECT '3', '1' FROM dual UNION ALL 
SELECT '4', '2' FROM dual UNION ALL 
SELECT '5', '4' FROM dual UNION ALL 
SELECT '6', '2' FROM dual 
)
SELECT top_organize_id fid,wm_concat(organize_id) cid FROM(
SELECT organize_id,connect_by_root(organize_id) top_organize_id
FROM  t_sys_organize
CONNECT BY PRIOR organize_id= top_organize_id
)
GROUP BY top_organize_id;  FID          CID
---------- --------------------
  1            1,2,4,6,3,5
  2            2,4,5,6    
  3            3          
  4            4,5        
  5            5          
  6            6          

Answer 4

start with 条件connect by prior 

Answer 5

谢谢，不过你的cid、tid、fid、oid搞得我好晕，
能不能换成这个需求里面的ORGANIZE_ID TOP_ORGANIZE_ID呢？呵呵，先谢谢啦~

Answer 6

如果对连接顺序没要求的话,2楼的就可以了.
gelyon的with只是提供数据,不是写死.

Answer 7

先赞下一楼的
呵呵 我的oid就是organize_id ，tid就是top_organize_id
其他的cid fid 都是别名

Answer 8

17:03:38 SQL> edi
已写入 file afiedt.buf  1  with tb as
  2  (
  3  select   1 organize_id,null top_organize_id from dual union all
  4  select   2,1 from dual union all
  5  select   3,1 from dual union all
  6  select   4,2 from dual union all
  7  select   5,4 from dual union all
  8  select   6,2 from dual)
  9  select r_id,wm_concat(organize_id) r_id2
 10  from (
 11  select organize_id,top_organize_id,connect_by_root(organize_id) r_id
 12  from tb
 13  connect by prior organize_id=top_organize_id
 14  )
 15* group by r_id
17:03:42 SQL> /      R_ID R_ID2
---------- ------------------------------
         1 1,2,4,6,3,5
         2 2,4,5,6
         3 3
         4 4,5
         5 5
         6 6已选择6行。

Answer 9

这哪写死 你用的时候把前面的去掉就可以了，这只是with tb as 一个临时集合而已你用的时候就捡后面的sql就行了  把表替换你的就行

Answer 10

谢谢大家！
结合一二楼的方法，我顺利查出
    select wm_concat(son_organize_id) son_organize_id
    from
      (select connect_by_root(organize_id) ORGANIZE_ID, t.organize_id son_organize_id
      from t_sys_organize t
      start with t.organize_id=1
      connect by prior organize_id= t.top_organize_id)
    group by ORGANIZE_ID;
但是，当我把这个结果作为查询条件的时候，却出现问题了select * from t_sys_organize where organize_id in (
    select wm_concat(son_organize_id) son_organize_id
    from
      (select connect_by_root(organize_id) ORGANIZE_ID, t.organize_id son_organize_id
      from t_sys_organize t
      start with t.organize_id=1
      connect by prior organize_id= t.top_organize_id)
    group by ORGANIZE_ID
   )   ;ora-01722:invalid number这是为什么呢？该怎么改呢？
ORGANIZE_ID是NUMBER(16)类型的

Answer 11

原因是where organize_id in (select wm_concat(son_organize_id) son_organize_id from。
wm_concat()只是连接字串的函数！因此连接后是字串，而你外层organize_id 是number ，因此报错！解决：
where organize_id||'' in(.....)
或者 to_char(organize_id) in (.....)

Answer 12

没数据是正常的，因为你wm_concat(son_organize_id)函数连接了organize_id，导致你基表t_sys_organize中的organize_id不在in集合中

Answer 13

贴出你详细信息，或者你内层结果查询出的son_organize_id是哪些数据，贴出来看看，还有你原表t_sys_organize中的organize_id是些什么样的数据下班了，祝你好运！

Answer 14

--数据类型匹配问题 试试这样
select * from t_sys_organize where ','||organize_id||',' in (
  select ','||trim(wm_concat(son_organize_id))||','
  from
  (select connect_by_root(organize_id) ORGANIZE_ID, t.organize_id son_organize_id
  from t_sys_organize t
  start with t.organize_id=1
  connect by prior organize_id= t.top_organize_id)
  group by ORGANIZE_ID
  ) ;

Answer 15

--你的试试SQL> edi
已写入 file afiedt.buf  1   with tb as
  2   (
  3   select   1 organize_id,null top_organize_id from dual union all
  4   select   2,1 from dual union all
  5   select   3,1 from dual union all
  6   select   4,2 from dual union all
  7   select   5,4 from dual union all
  8   select   6,2 from dual),
  9   tb3 as (
 10   select r_id,wm_concat(organize_id) r_id2
 11   from (
 12   select organize_id,top_organize_id,connect_by_root(organize_id) r_id
 13   from tb
 14   start with organize_id=1
 15   connect by prior organize_id=top_organize_id
 16   )
 17   group by r_id
 18   )
 19   select a.organize_id from tb a,tb3 b
 20* where instr(','||trim(b.r_id2)||',',','||a.organize_id||',')>0
SQL> /ORGANIZE_ID
-----------
          1
          2
          3
          4
          5
          6已选择6行。

Answer 16

oracle有自带的connect by prior ，mysql就只能递归了噢

Answer 17

t_sys_organize 表的数据如下：ORGANIZE_ID TOP_ORGANIZE_ID ORG_CODE
1 44000000
11 1 44010000
12 1 44020000
13 1 44030000
14 1 44040000
15 1 44050000
16 1 44060000
17 1 44070000
18 1 44080000
19 1 44090000
20 1 44100000
21 1 44110000
22 1 44120000
23 1 44130000
24 1 44140000
25 1 44150000
26 1 44160000
27 1 44170000
28 1 44180000
29 1 44190000
30 1 44200000
31 1 44210000
32 1 44220000
33 1 44230000
34 1 44240000
35 1 44250000
36 1 44260000
37 1 44270000
38 1 44280000
39 1 44290000
40 1 44300000
41 1 44310000
42 1 44320000
88 14 44040001
89 13 44030001ORGANIZE_ID是NUMBER(16)类型的 
我想查的是：
select * from t_sys_organize where organize_id in (
  select wm_concat(son_organize_id) son_organize_id
  from
  (select connect_by_root(organize_id) ORGANIZE_ID, t.organize_id son_organize_id
  from t_sys_organize t
  start with t.organize_id=1
  connect by prior organize_id= t.top_organize_id)
  group by ORGANIZE_ID
  ) ;

Answer 18

那不就是相当于查叶子节点么?不需要这么麻烦啊,直接connect_by_isleaf不就可以了.

Answer 19

有两张表Q_EXIT_ENTRY 进出境信息表, T_SYS_ORGANIZE 组织机构表
两表通过organize_id关联，通过以下sql查出某个机构及其下属机构进出境的信息
 select QEE.*, TSO.ORG_NAME_ZH TSO_ORG_NAME_ZH
 from Q_EXIT_ENTRY QEE, T_SYS_ORGANIZE TSO
 where QEE.organize_id = TSO.organize_id(+) 
   and TSO.organize_id in(----我打算在这里查出organize_id及其子孙节点的organize_id----)
谢谢大家先了！急用啊~

Answer 20

这么给你说吧，你内层
select wm_concat(son_organize_id) son_organize_id
  from
  (select connect_by_root(organize_id) ORGANIZE_ID, t.organize_id son_organize_id
  from t_sys_organize t
  start with t.organize_id=1
  connect by prior organize_id= t.top_organize_id)
  group by ORGANIZE_ID查询出来的结果就是这个值：
son_organize_id=1,11,12,49,88,16,18,20,22,31,30,29,28,27,26,25,24,23,42,41,40,39,38,37,36,35,34,33,32,21,19,17,15,14,13外层怎么给他匹配？所以没有结果产生！

Answer 21

select QEE.*, TSO.ORG_NAME_ZH TSO_ORG_NAME_ZH
 from Q_EXIT_ENTRY QEE, T_SYS_ORGANIZE TSO
 where QEE.organize_id = TSO.organize_id(+) 
   and TSO.organize_id in(select b.organize_id from T_SYS_ORGANIZE b start with b.organize_id=qee.organize_id connect by prior b.organize_id=b.top_organize_id)这样就可以了

Answer 22

上面写错了
select QEE.*, TSO.ORG_NAME_ZH TSO_ORG_NAME_ZH
 from Q_EXIT_ENTRY QEE, (select b.organize_id from T_SYS_ORGANIZE b start with b.organize_id=根机构代码 connect by prior b.organize_id=b.top_organize_id) TSO
 where QEE.organize_id(+) = TSO.organize_id 

Answer 23

上面还是写错了-_-@!
select QEE.*, TSO.ORG_NAME_ZH TSO_ORG_NAME_ZH
 from Q_EXIT_ENTRY QEE, (select b.organize_id,b.ORG_NAME_ZH from T_SYS_ORGANIZE b start with b.organize_id=根机构代码 connect by prior b.organize_id=b.top_organize_id) TSO
 where QEE.organize_id(+) = TSO.organize_id