请教一个sql语句

Question

数据结构
ID       BNO           NEW_BNO    NAME
1        1001          10001       XX   
2        1002          10003       XX   
3        10001         10008       XX  
4        10008                     XX 
5        10009                     XX 
6        10003         10003       XX   
如上所示
ID为1的数据中 NEW_BNO 是 ID为3 的BNO,而ID为3的NEW_BNO 是 ID为4的BNO;ID为2的也一样，只是少了一次
这样的循环有可能是很多次，也可能没有，如ID为5的怎么样能取出
ID=4，ID=5，ID=6的数据，也就是有循环的取最后一次，没有循环的取自己
  
                    请大家帮忙看看，谢谢！

Answer 1

select * from table t where NEW_BNO is null or NEW_BNO=id or not exists(select 1 
from table b where b.id=t.NEW_BNO)

Answer 2

不好意思，刚才写错了一点，主键是BNO，并没有ID这个字段

Answer 3

select BNO, NEW_BNO, NAME
from tab
where connect_by_isleaf=1
start with BNO='1001'
connect by proir NEW_BNO =BNO

Answer 4

--是这样吗？
  1  with tb as(
  2  SELECT 1001 BNO,10001 NEW_BNO,'XX' NAME FROM DUAL UNION ALL
  3  SELECT 10001,10003,'XX' FROM DUAL UNION ALL
  4  SELECT 10001,10008,'XX' FROM DUAL UNION ALL
  5  SELECT 10008,NULL,'XX' FROM DUAL UNION ALL
  6  SELECT 10009,NULL,'XX' FROM DUAL UNION ALL
  7  SELECT 10003,10013,'XX' FROM DUAL)
  8  SELECT * FROM TB
  9  WHERE CONNECT_BY_ISLEAF=1
 10  START WITH BNO=1001
 11* CONNECT BY BNO = PRIOR NEW_BNO
SQL> /       BNO    NEW_BNO NA
---------- ---------- --
     10003      10013 XX
     10008            XX

Answer 5

BNO='1001'   BNO不作为条件，我需要查出来所有的

Answer 6

BNO不作为条件，我需要查出来所有的

Answer 7

附上数据
create table info
(
   BNO varchar2(32), --编号
   NEW_BNO varchar2(32), --新的编号
   NAME    varchar2(32) --名称
   primary key(BNO)
);
 
数据没有先后顺序
INSERT INTO info VALUES  ('1001','10001','a1');
INSERT INTO info VALUES  ('1002','10003','a2');
INSERT INTO info VALUES  ('10001','10008','a44');
INSERT INTO info VALUES  ('10008','','a22');
INSERT INTO info VALUES  ('10009','','a21');
INSERT INTO info VALUES  ('10003','','a21');需要查出BNO=10008、10009、10003这三条

Answer 8

create table info
(
  BNO varchar2(32), --编号
  NEW_BNO varchar2(32), --新的编号
  NAME varchar2(32) ,--名称
  primary key(BNO)
);
 INSERT INTO info VALUES ('1001','10001','a1');
INSERT INTO info VALUES ('1002','10003','a2');
INSERT INTO info VALUES ('10001','10008','a44');
INSERT INTO info VALUES ('10008','','a22');
INSERT INTO info VALUES ('10009','','a21');
INSERT INTO info VALUES ('10003','','a21');
select DISTINCT BNO, NEW_BNO, NAME
from info a
where connect_by_isleaf=1
start with (EXISTS(SELECT 1 FROM info WHERE a.new_bno=bno)) 
        OR EXISTS (SELECT 1 FROM info WHERE bno=a.bno) 
connect by prior NEW_BNO =BNO结果：BNO   NEW_BNO  NAME
-----------------------
10003          a21
10008          a22
10009          a21

Answer 9

/* Formatted on 2010/11/17 08:57 (Formatter Plus v4.8.8) */
SELECT a.bno, a.new_bno, a.NAME
  FROM info a
 WHERE NOT EXISTS (SELECT 'x'
                     FROM info b
                    WHERE b.bno = a.new_bno) OR a.new_bno IS NULL不知是否够严谨了

Answer 10

我9I数据库不能用 connect_by_isleaf。囧

Answer 11

你这个不够严谨 条件OR a.new_bno IS NULL 这个不一定是空，有可能是最后一个节点的new_bno没有对应的bnoconnect_by_isleaf是10g新增功能，不过9i可以通过分析函数来实现达到connect_by_isleaf=1的功能

Answer 12

不是通过 not esists排除了有节点的吗？
难道还有第三种情况？

Answer 13

create table info
(
  BNO varchar2(32), --编号
  NEW_BNO varchar2(32), --新的编号
  NAME varchar2(32) ,--名称
  WORKDATE  DATE, --工作时间
  primary key(BNO)
);
 
INSERT INTO info VALUES ('20001','9007','a1',to_date('2010-08-09','yyyy-mm-dd'));
INSERT INTO info VALUES ('1001','10001','a1',to_date('2010-09-09','yyyy-mm-dd'));
INSERT INTO info VALUES ('1002','10003','a2',to_date('2010-09-09','yyyy-mm-dd'));
INSERT INTO info VALUES ('10001','10008','a44',to_date('2010-09-09','yyyy-mm-dd'));
INSERT INTO info VALUES ('9007','','a44',to_date('2010-09-09','yyyy-mm-dd'));
INSERT INTO info VALUES ('10008','','a22',to_date('2010-09-10','yyyy-mm-dd'));
INSERT INTO info VALUES ('10009','','a21',to_date('2010-09-10','yyyy-mm-dd'));
INSERT INTO info VALUES ('10003','','a21',to_date('2010-09-10','yyyy-mm-dd'));
还有一个难点就是需要以时间为条件 ，比如从九月的数据为原始数据开始查询，需要去掉9月里数据的BNO是9月前数据的NEW_BNO这部分数据，9月或者9月后的还是取最后一次那条

Answer 14

分析楼主需求，表中存在三种数据：
1.不存在NEW_BNO
2.存在NEW_BNO且BNO=NEW_BNO
3.存在NEW_BNO且BNO<>NEW_BNO
其中第3种数据是不显示的数据，所以该需求不需要使用CONNECT BY子句SELECT * FROM TB WHERE NEW_BNO IS NULL OR BNO = NEW_BNO

Answer 15

不好意思，我没把问题描述清楚，需要以时间为条件 ，比如从九月的数据为原始数据开始查询，需要去掉9月里数据的BNO是9月前数据的NEW_BNO这部分数据，9月或者9月后的还是取最后一次那条，

Answer 16

这样就需要使用connect子句了。-- 测试用数据构造部分
WITH INFO AS (
SELECT '20001'BNO,'9007' NEW_BNO,'a1' NAME,to_date('2010-08-09','yyyy-mm-dd') WORKDATE FROM DUAL UNION ALL
SELECT '1001','10001','a1',to_date('2010-09-09','yyyy-mm-dd') FROM DUAL UNION ALL
SELECT '1002','10003','a2',to_date('2010-09-09','yyyy-mm-dd') FROM DUAL UNION ALL
SELECT '10001','10008','a44',to_date('2010-09-09','yyyy-mm-dd') FROM DUAL UNION ALL
SELECT '9007','','a44',to_date('2010-09-09','yyyy-mm-dd') FROM DUAL UNION ALL
SELECT '10008','','a22',to_date('2010-09-10','yyyy-mm-dd') FROM DUAL UNION ALL
SELECT '10009','','a21',to_date('2010-09-10','yyyy-mm-dd') FROM DUAL UNION ALL
SELECT '10003','','a21',to_date('2010-09-10','yyyy-mm-dd') FROM DUAL 
)
-- SQL部分，where子查询的逻辑是递归查找9月1日前的项目的所有新项
SELECT *
  FROM INFO
 WHERE (NEW_BNO IS NULL OR NEW_BNO = BNO)
   AND BNO NOT IN
       (SELECT DISTINCT BNO
          FROM INFO
         START WITH WORKDATE < TO_DATE('2010-09-01', 'YYYY-MM-DD')
        CONNECT BY BNO = PRIOR NEW_BNO);