connect by prior的问题！

Question

create table T_SORT
(
  PK_AUTO_ID        VARCHAR2(20) not null,
  PID               VARCHAR2(20),
  NAME              VARCHAR2(500) not null,
  SORT              VARCHAR2(20),
  CHECK_FLAG        VARCHAR2(2) not null,
  PUBLIC_TAG        VARCHAR2(2) not null,
  FK_TEMPLATE_ID    VARCHAR2(500),
  FK_POST_USER_ID   VARCHAR2(20),
  POST_TIME         DATE,
  FK_UPDATE_USER_ID VARCHAR2(20),
  UPDATE_TIME       DATE,
  IS_FINAL          NUMBER default -1 not null,
  INDEX_NUM         NUMBER,
  STATUS            NUMBER not null,
  VALID_TIME        VARCHAR2(20)
);insert into t_sort (PK_AUTO_ID, PID, NAME, SORT, CHECK_FLAG, PUBLIC_TAG, FK_TEMPLATE_ID, FK_POST_USER_ID, POST_TIME, FK_UPDATE_USER_ID, UPDATE_TIME, IS_FINAL, INDEX_NUM, STATUS, VALID_TIME)
values ('9991', '9990', '一级分类', '', '0', '0', '', '1', to_date('30-03-2010', 'dd-mm-yyyy'), '', null, -1, null, 1, '2010-02-03');insert into t_sort (PK_AUTO_ID, PID, NAME, SORT, CHECK_FLAG, PUBLIC_TAG, FK_TEMPLATE_ID, FK_POST_USER_ID, POST_TIME, FK_UPDATE_USER_ID, UPDATE_TIME, IS_FINAL, INDEX_NUM, STATUS, VALID_TIME)
values ('9996', '9992', '三级分类', '', '0', '0', '', '1', to_date('30-03-2010', 'dd-mm-yyyy'), '', null, 1, null, 1, '2010-04-02');insert into t_sort (PK_AUTO_ID, PID, NAME, SORT, CHECK_FLAG, PUBLIC_TAG, FK_TEMPLATE_ID, FK_POST_USER_ID, POST_TIME, FK_UPDATE_USER_ID, UPDATE_TIME, IS_FINAL, INDEX_NUM, STATUS, VALID_TIME)
values ('9992', '9991', '二级分类', '', '0', '0', '', '1', to_date('30-03-2010', 'dd-mm-yyyy'), '', null, -1, null, 1, '2010-03-01');insert into t_sort (PK_AUTO_ID, PID, NAME, SORT, CHECK_FLAG, PUBLIC_TAG, FK_TEMPLATE_ID, FK_POST_USER_ID, POST_TIME, FK_UPDATE_USER_ID, UPDATE_TIME, IS_FINAL, INDEX_NUM, STATUS, VALID_TIME)
values ('9994', '9992', '三级分类2', '', '0', '0', '', '1', to_date('30-03-2010', 'dd-mm-yyyy'), '', null, 1, null, 1, '2010-04-02');insert into t_sort (PK_AUTO_ID, PID, NAME, SORT, CHECK_FLAG, PUBLIC_TAG, FK_TEMPLATE_ID, FK_POST_USER_ID, POST_TIME, FK_UPDATE_USER_ID, UPDATE_TIME, IS_FINAL, INDEX_NUM, STATUS, VALID_TIME)
values ('9993', '9992', '三级分类3', '', '0', '0', '', '1', to_date('31-03-2010', 'dd-mm-yyyy'), '', null, 1, null, 1, '2010-04-02');insert into t_sort (PK_AUTO_ID, PID, NAME, SORT, CHECK_FLAG, PUBLIC_TAG, FK_TEMPLATE_ID, FK_POST_USER_ID, POST_TIME, FK_UPDATE_USER_ID, UPDATE_TIME, IS_FINAL, INDEX_NUM, STATUS, VALID_TIME)
values ('9995', '9992', '三级分类4', '', '0', '0', '', '1', to_date('1-04-2010', 'dd-mm-yyyy'), '', null, 1, null, 1, '2010-04-02');insert into t_sort (PK_AUTO_ID, PID, NAME, SORT, CHECK_FLAG, PUBLIC_TAG, FK_TEMPLATE_ID, FK_POST_USER_ID, POST_TIME, FK_UPDATE_USER_ID, UPDATE_TIME, IS_FINAL, INDEX_NUM, STATUS, VALID_TIME)
values ('9998', '9991', '三级分类4', '', '0', '0', '', '1', to_date('1-04-2010', 'dd-mm-yyyy'), '', null, 1, null, 1, '2010-04-02');select distinct ts.pk_auto_id,ts.pid,ts.name,ts.valid_time from t_sort ts 
where ts.status='1' 
 --and ceil(to_date(ts.valid_time,'yyyy-mm-dd')-sysdate)<0 
start with ts.pk_auto_id/*='9991'*/ in (
      select s.pk_auto_id from t_sort s where s.status='1' and ceil(to_date(s.valid_time,'yyyy-mm-dd')-sysdate)<0     
)
connect by prior ts.pk_auto_id=ts.pid;我这条语句得不出我想要的结果。我要的是如果pid的失效时间valid_time小于等于今天的话，那么会把pid的记录以及pid下面的所有子级目录都查出来。如果本条记录失效时间valid_time小于等于今天，而上面所有的父级的valid_time都大于等于今天的话，只查询本条记录出来。大家看看我的sql如果改进才能得到我想要的结果呢？

Answer 1

本帖最后由 wildwave 于 2010-03-31 10:54:15 编辑

  

Answer 2

不好意思啊，没有说完啊：如果pid的失效时间valid_time小于等于今天的话，那么会把pid的记录以及pid下面的所有子级目录都查出来。如果本条记录是最底层子级目录的话，而且失效时间valid_time小于等于今天，而上面所有的父级的valid_time都大于等于今天的话，只查询本条记录出来。

Answer 3

如果pid的失效时间valid_time小于等于今天的话，那么会把pid的记录以及pid下面的所有子级目录都查出来。 如果本条记录是最底层子级目录的话，而且失效时间valid_time小于等于今天，而上面所有的父级的valid_time都大于等于今天的话，只查询本条记录出来。

Answer 4

想要查询的结果不对啊。我上面有建表sql，也有insert的数据sql，后面有自己写的查询sql语句，你跑下，看对不对啊?

Answer 5

我的天..
我当然看过了，几条记录的validtime大于当前日期，但是它们都是符合条件1的记录的下级记录
所以没有错
下次再看，没时间了

Answer 6

with the_tree as (
                 select ln, route, pk_auto_id, pid, name, to_date(valid_time, 'yyyy-mm-dd') valid_time,
                        max(ln) over() max_ln
                   from (
                            select level ln, sys_connect_by_path(pk_auto_id, '/') route, t.*
                              from t_sort t
                            -- where to_date(valid_time, 'yyyy-mm-dd') <= sysdate
                             start with pk_auto_id = '9991'
                           connect by prior pk_auto_id =  pid                          
                        )
                 )
--如果本条记录是最底层子级目录的话，而且失效时间valid_time小于等于今天，而上面所有的父级的valid_time都大于等于今天的话，只查询本条记录出来。                 
select *
  from the_tree t
 where ln = max_ln                                                       --末级目录
   and valid_time < trunc(sysdate, 'dd')                                 --失效时间valid_time小于等于今天
   and not exists (                                                      --所有父级的valid_time都大于等于今天
                   select 1
                     from the_tree
                    where valid_time < trunc(sysdate, 'dd')
                      and ln < t.max_ln
                  )
                  
union                   
                
--如果pid的失效时间valid_time小于等于今天的话，那么会把pid的记录以及pid下面的所有子级目录都查出来。
select *
  from the_tree t
 where exists(               
                select 1
                  from the_tree x
                 where ln < max_ln                                  --不是末级目录
                   and valid_time  <= trunc(sysdate, 'dd')          --pid的失效时间valid_time小于等于今天         
                   and instr('/' || t.route || '/', '/' || x.pk_auto_id || '/') > 0 --查询pid及pid下所有目录
             )