第一次使用Oracle递归查询语句(start with connect by),遇到了一个比较奇怪的问题,有一个如下的SQL,查询结果如下select sobid as id, objid as childid,level
from hrp1001 t
where t.otype = 'O'
and t.plvar = '01'
and t.sclas = 'O'
and t.RSIGN = 'A'
and t.relat = '002'
and t.isdelete = '1'
and to_date('2011-06-30', 'yyyy-mm-dd') between t.begda and t.endda --and t.sobid = '50007206'
start with t.sobid ='50000914'
connect by nocycle prior OBJID = sobid结果
50000914 50002238 1
50000914 50002239 1
50000914 50002237 1
50002238 50009674 2
50002238 50009676 2
50002238 50009675 2
50002238 50009673 2
50002238 50009674 2
50002238 50009673 2
50002238 50009676 2
50002238 50009675 2
50002239 50008748 2
50002239 50009024 2
50002239 50007147 2
50002239 50002241 2
50002239 50005300 2
50002240 50007448 2
50002240 50007447 2标示红颜色的结果重复,标示粉红色的结果在查询的第一层结果中根本就没有该节点,数据库中该树形结果正常,数据肯定是没有问题的,是不是Oracle的这个用法有问题呀?期待高手。
from hrp1001 t
where t.otype = 'O'
and t.plvar = '01'
and t.sclas = 'O'
and t.RSIGN = 'A'
and t.relat = '002'
and t.isdelete = '1'
and to_date('2011-06-30', 'yyyy-mm-dd') between t.begda and t.endda --and t.sobid = '50007206'
start with t.sobid ='50000914'
connect by nocycle prior OBJID = sobid结果
50000914 50002238 1
50000914 50002239 1
50000914 50002237 1
50002238 50009674 2
50002238 50009676 2
50002238 50009675 2
50002238 50009673 2
50002238 50009674 2
50002238 50009673 2
50002238 50009676 2
50002238 50009675 2
50002239 50008748 2
50002239 50009024 2
50002239 50007147 2
50002239 50002241 2
50002239 50005300 2
50002240 50007448 2
50002240 50007447 2标示红颜色的结果重复,标示粉红色的结果在查询的第一层结果中根本就没有该节点,数据库中该树形结果正常,数据肯定是没有问题的,是不是Oracle的这个用法有问题呀?期待高手。
50004715 50007206 1
50004715 50004734 1
50004715 50004720 1
50004715 50008120 1
50004720 50009078 2
50004720 50007381 2
50004720 50008122 2
50004734 50008563 2
50004734 50008563 3
50004734这个节点在Level2和3都递归的查了一次,这又是什么原因呢,在怀疑Oracle的这个用法有bug了,有人遇到过相同的问题吗?
create table HRP1001
(
JAVAID VARCHAR2(32) not null,
ISDELETE CHAR(1),
UNAME VARCHAR2(32),
AEDTM DATE,
ENDDA DATE,
BEGDA DATE,
OTYPE VARCHAR2(2),
OBJID VARCHAR2(32),
PLVAR VARCHAR2(2),
RSIGN VARCHAR2(1),
RELAT VARCHAR2(3),
ISTAT VARCHAR2(1),
PRIOX VARCHAR2(2),
VARYF VARCHAR2(10),
SEQNR VARCHAR2(3),
INFTY VARCHAR2(4),
OTJID VARCHAR2(10),
SUBTY VARCHAR2(4),
SCLAS VARCHAR2(2),
SOBID VARCHAR2(32),
PROZT NUMBER(6,2),
ADATANR VARCHAR2(32)
)第二个问题所涉及的数据
insert into hrp1001 (JAVAID, ISDELETE, UNAME, AEDTM, ENDDA, BEGDA, OTYPE, OBJID, PLVAR, RSIGN, RELAT, ISTAT, PRIOX, VARYF, SEQNR, INFTY, OTJID, SUBTY, SCLAS, SOBID, PROZT, ADATANR)
values ('9487', '1', '180049', to_date('17-05-2009', 'dd-mm-yyyy'), to_date('31-12-9999', 'dd-mm-yyyy'), to_date('13-05-2009', 'dd-mm-yyyy'), 'O', '50008120', '01', 'A', '002', '1', '', 'O 50004715', '0', '1001', 'O 50008120', 'A002', 'O', '50004715', 0.00, '');insert into hrp1001 (JAVAID, ISDELETE, UNAME, AEDTM, ENDDA, BEGDA, OTYPE, OBJID, PLVAR, RSIGN, RELAT, ISTAT, PRIOX, VARYF, SEQNR, INFTY, OTJID, SUBTY, SCLAS, SOBID, PROZT, ADATANR)
values ('6237', '1', '100128', to_date('25-01-2007', 'dd-mm-yyyy'), to_date('31-12-9999', 'dd-mm-yyyy'), to_date('01-01-2007', 'dd-mm-yyyy'), 'O', '50004720', '01', 'A', '002', '1', '', 'O 50004715', '0', '1001', 'O 50004720', 'A002', 'O', '50004715', 0.00, '');insert into hrp1001 (JAVAID, ISDELETE, UNAME, AEDTM, ENDDA, BEGDA, OTYPE, OBJID, PLVAR, RSIGN, RELAT, ISTAT, PRIOX, VARYF, SEQNR, INFTY, OTJID, SUBTY, SCLAS, SOBID, PROZT, ADATANR)
values ('8547', '1', '100128', to_date('12-06-2008', 'dd-mm-yyyy'), to_date('31-12-9999', 'dd-mm-yyyy'), to_date('12-06-2008', 'dd-mm-yyyy'), 'O', '50007206', '01', 'A', '002', '1', '', 'O 50004715', '0', '1001', 'O 50007206', 'A002', 'O', '50004715', 0.00, '');insert into hrp1001 (JAVAID, ISDELETE, UNAME, AEDTM, ENDDA, BEGDA, OTYPE, OBJID, PLVAR, RSIGN, RELAT, ISTAT, PRIOX, VARYF, SEQNR, INFTY, OTJID, SUBTY, SCLAS, SOBID, PROZT, ADATANR)
values ('6260', '1', '180049', to_date('17-05-2009', 'dd-mm-yyyy'), to_date('31-12-9999', 'dd-mm-yyyy'), to_date('13-05-2009', 'dd-mm-yyyy'), 'O', '50004734', '01', 'A', '002', '1', '', 'O 50004715', '0', '1001', 'O 50004734', 'A002', 'O', '50004715', 0.00, '');insert into hrp1001 (JAVAID, ISDELETE, UNAME, AEDTM, ENDDA, BEGDA, OTYPE, OBJID, PLVAR, RSIGN, RELAT, ISTAT, PRIOX, VARYF, SEQNR, INFTY, OTJID, SUBTY, SCLAS, SOBID, PROZT, ADATANR)
values ('9974', '1', '180049', to_date('17-05-2009', 'dd-mm-yyyy'), to_date('31-12-9999', 'dd-mm-yyyy'), to_date('13-05-2009', 'dd-mm-yyyy'), 'O', '50009078', '01', 'A', '002', '1', '', 'O 50004720', '0', '1001', 'O 50009078', 'A002', 'O', '50004720', 0.00, '');insert into hrp1001 (JAVAID, ISDELETE, UNAME, AEDTM, ENDDA, BEGDA, OTYPE, OBJID, PLVAR, RSIGN, RELAT, ISTAT, PRIOX, VARYF, SEQNR, INFTY, OTJID, SUBTY, SCLAS, SOBID, PROZT, ADATANR)
values ('8714', '1', '100128', to_date('11-07-2008', 'dd-mm-yyyy'), to_date('31-12-9999', 'dd-mm-yyyy'), to_date('01-07-2008', 'dd-mm-yyyy'), 'O', '50007381', '01', 'A', '002', '1', '', 'O 50004720', '0', '1001', 'O 50007381', 'A002', 'O', '50004720', 0.00, '');insert into hrp1001 (JAVAID, ISDELETE, UNAME, AEDTM, ENDDA, BEGDA, OTYPE, OBJID, PLVAR, RSIGN, RELAT, ISTAT, PRIOX, VARYF, SEQNR, INFTY, OTJID, SUBTY, SCLAS, SOBID, PROZT, ADATANR)
values ('9494', '1', '180049', to_date('17-05-2009', 'dd-mm-yyyy'), to_date('31-12-9999', 'dd-mm-yyyy'), to_date('13-05-2009', 'dd-mm-yyyy'), 'O', '50008122', '01', 'A', '002', '1', '', 'O 50004720', '0', '1001', 'O 50008122', 'A002', 'O', '50004720', 0.00, '');insert into hrp1001 (JAVAID, ISDELETE, UNAME, AEDTM, ENDDA, BEGDA, OTYPE, OBJID, PLVAR, RSIGN, RELAT, ISTAT, PRIOX, VARYF, SEQNR, INFTY, OTJID, SUBTY, SCLAS, SOBID, PROZT, ADATANR)
values ('9679', '1', '180049', to_date('19-02-2009', 'dd-mm-yyyy'), to_date('31-12-9999', 'dd-mm-yyyy'), to_date('13-02-2009', 'dd-mm-yyyy'), 'O', '50008563', '01', 'A', '002', '1', '', 'O 50004734', '0', '1001', 'O 50008563', 'A002', 'O', '50004734', 0.00, '');
start with t.objid ='50000914'
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0