oracle递归，只显示up_id=根节点的问题

Question

部门表(bumen)
id（主键）                        up_id（上级id）                    bumen
151F466EBB2846FA91830D98682ADB8D                                     公司
4BBDBA04DDBC4A58A57302374A045CBB  151F466EBB2846FA91830D98682ADB8D   A部门
418C737F7C9F4D3490B521F7E0F85377  4BBDBA04DDBC4A58A57302374A045CBB   A部门->a班组
E02F9A3530C14EC9B905F7E016A59B05  4BBDBA04DDBC4A58A57302374A045CBB   A部门->b班组
7E98ACEF45634BA08C50CB5544FA60BC  E02F9A3530C14EC9B905F7E016A59B05   A部门->b班组->a小队
285470122951413F96B85B0664FB63C6  151F466EBB2846FA91830D98682ADB8D   B部门
AF489E4D28E44889A0680F7BEC76BA6E  285470122951413F96B85B0664FB63C6   B部门->a班组
1527372AC7A94139A02A2F876EA0B68F  151F466EBB2846FA91830D98682ADB8D   C部门
记录表(jilu)
id（主键）                        bumen（部门，取的bumen表id）       neirong（内容）         riqi（日期）
B0CC08491CF54E56811F73C65B79E115  4BBDBA04DDBC4A58A57302374A045CBB   对A部门提出**考核       2010/1/1
E60CDB3D55E44B4082EEABE3B7AFCC0C  418C737F7C9F4D3490B521F7E0F85377   对A部门a班组提出**考核  2010/1/2
EDC0028B909345B8B19899A88DF96675  E02F9A3530C14EC9B905F7E016A59B05   对A部门b班组提出**考核  2010/1/3
EA72F52459EA4197B79D2BE4471270FE  AF489E4D28E44889A0680F7BEC76BA6E   对B部门a班组提出**考核  2010/1/4
8F5D9BF1B7454D73B70B6B95052D250F  1527372AC7A94139A02A2F876EA0B68F   对C部门提出**考核       2010/2/1我想实现，以部门为单位（即只显示up_id=根节点的数据），如下：id                                bumen
4BBDBA04DDBC4A58A57302374A045CBB  A部门
285470122951413F96B85B0664FB63C6  B部门
我写了以下语句：
select b.id,b.bumen from a jilu a left join bumen b on a.bumen=b.id where riqi>=to_date('2010-01-01 00:00:00','yyyy-MM-dd hh24:mi:ss') and riqi<=to_date('2010-01-31 23:59:59','yyyy-MM-dd hh24:mi:ss')
可是现实结果却是：
id                                bumen
4BBDBA04DDBC4A58A57302374A045CBB  A部门
418C737F7C9F4D3490B521F7E0F85377  A部门->a班组
E02F9A3530C14EC9B905F7E016A59B05  A部门->b班组
7E98ACEF45634BA08C50CB5544FA60BC  A部门->b班组->a小队
285470122951413F96B85B0664FB63C6  B部门
AF489E4D28E44889A0680F7BEC76BA6E  B部门->a班组
请问各位高人如何实现如下效果（即只显示up_id=根节点的数据）：
id                                bumen
4BBDBA04DDBC4A58A57302374A045CBB  A部门
285470122951413F96B85B0664FB63C6  B部门

Answer 1

--确保公司的up_id为null
select b.id, b.bumen
  from a jilu a
  left join (select id,bumen from bumen t where 
    up_id is not null start with up_id is null 
    connect by prior id=up_id and level=2) b
    on a.bumen = b.id
 where riqi >= to_date('2010-01-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
   and riqi <= to_date('2010-01-31 23:59:59', 'yyyy-MM-dd hh24:mi:ss')

Answer 2

不用connect by了，只需要用where条件就可以。
楼主想复杂了吧

Answer 3

谢谢tangren的解答，但是我查询出来却是
id                                bumen 
没有数据                               没有数据 
285470122951413F96B85B0664FB63C6  B部门
也就是如果这条记录恰好在level=2上，则有显示，如果这条记录所在部门比level=2深，比如A部门->b班组->a小队，则没有记录，是一个空行。

Answer 4

select * from tb
where up_id='151F466EBB2846FA91830D98682ADB8D'

Answer 5

with bumen as(
select '151F466EBB2846FA91830D98682ADB8D' id,   null                              up_id,  '公司'                bumen from dual union all  
select '4BBDBA04DDBC4A58A57302374A045CBB' id,  '151F466EBB2846FA91830D98682ADB8D' up_id,  'A部门'               bumen from dual union all
select '418C737F7C9F4D3490B521F7E0F85377' id,  '4BBDBA04DDBC4A58A57302374A045CBB' up_id,  'A部门->a班组'        bumen from dual union all
select 'E02F9A3530C14EC9B905F7E016A59B05' id,  '4BBDBA04DDBC4A58A57302374A045CBB' up_id,  'A部门->b班组'        bumen from dual union all
select '7E98ACEF45634BA08C50CB5544FA60BC' id,  'E02F9A3530C14EC9B905F7E016A59B05' up_id,  'A部门->b班组->a小队' bumen from dual union all 
select '285470122951413F96B85B0664FB63C6' id,  '151F466EBB2846FA91830D98682ADB8D' up_id,  'B部门'               bumen from dual union all
select 'AF489E4D28E44889A0680F7BEC76BA6E' id,  '285470122951413F96B85B0664FB63C6' up_id,  'B部门->a班组'        bumen from dual union all
select '1527372AC7A94139A02A2F876EA0B68F' id,  '151F466EBB2846FA91830D98682ADB8D' up_id,  'C部门'               bumen from dual),
jilu as(
select 'B0CC08491CF54E56811F73C65B79E115' id,  '4BBDBA04DDBC4A58A57302374A045CBB' bumen, '对A部门提出**考核'       neirong, date '2010-1-1' riqi from dual union all 
select 'E60CDB3D55E44B4082EEABE3B7AFCC0C' id,  '418C737F7C9F4D3490B521F7E0F85377' bumen, '对A部门a班组提出**考核'  neirong, date '2010-1-2' riqi from dual union all 
select 'EDC0028B909345B8B19899A88DF96675' id,  'E02F9A3530C14EC9B905F7E016A59B05' bumen, '对A部门b班组提出**考核'  neirong, date '2010-1-3' riqi from dual union all 
select 'EA72F52459EA4197B79D2BE4471270FE' id,  'AF489E4D28E44889A0680F7BEC76BA6E' bumen, '对B部门a班组提出**考核'  neirong, date '2010-1-4' riqi from dual union all 
select '8F5D9BF1B7454D73B70B6B95052D250F' id,  '1527372AC7A94139A02A2F876EA0B68F' bumen, '对C部门提出**考核'       neirong, date '2010-2-1' riqi from dual)select b.id, b.bumen
  from jilu a
  join (select id,bumen from bumen t where 
    up_id is not null start with up_id is null 
    connect by prior id=up_id and level=2) b
    on a.bumen = b.id
 where riqi >= to_date('2010-01-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
   and riqi <= to_date('2010-01-31 23:59:59', 'yyyy-MM-dd hh24:mi:ss')

Answer 6

您好，如果这样的话就没有以下记录了
id                                bumen 
4BBDBA04DDBC4A58A57302374A045CBB  A部门

Answer 7

因为
A部门->a班组 
A部门->b班组 
A部门->b班组->a小队
都属于A部门，而他们在sql语句中又有记录，所以我想让
id                                bumen 
4BBDBA04DDBC4A58A57302374A045CBB  A部门这条记录出现。

Answer 8

你执行一下7楼语句，就是如下结果
id                                bumen 
4BBDBA04DDBC4A58A57302374A045CBB  A部门 

Answer 9

对不起，我原始的需求描述有误，是这样的，应该没有下面记录：
select 'B0CC08491CF54E56811F73C65B79E115' id,  '4BBDBA04DDBC4A58A57302374A045CBB' bumen, '对A部门提出**考核'       neirong, date '2010-1-1' riqi from dual union all也就是A部门这个根部门下面没有记录，而A部门下面的子部门有记录，这样的话如何显示：
 id                                bumen 
4BBDBA04DDBC4A58A57302374A045CBB  A部门 抱歉，是我的失误。

Answer 10

--再写一个了
with mybumen as   
(SELECT a.lv, a.id, a.bumen, b.id id2, b.bumen bumen2, b.lv lv2
  FROM (SELECT LEVEL lv, id, bumen, sys_connect_by_path(id, ',') conn
          FROM bumen t
         START WITH up_id IS NULL
        CONNECT BY PRIOR id = up_id) a,
       (SELECT *
          FROM (SELECT LEVEL lv, id, bumen
                  FROM bumen t
                 START WITH up_id IS NULL
                CONNECT BY PRIOR id = up_id)
         WHERE lv = 2) b
 WHERE instr(a.conn, b.id) > 0)
SELECT distinct b.id2, b.bumen2
  FROM jilu a, mybumen b
 WHERE a.bumen = b.id AND
       riqi >= to_date('2010-01-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss') AND
       riqi <= to_date('2010-01-31 23:59:59', 'yyyy-MM-dd hh24:mi:ss');