为什么CONNECT BY查询的结果总是不对_connect by list of a query can only be prior or le-CSDN博客

在处理工作流查询时，遇到CONNECT BY查询导致的数据重复和内循环错误。问题在于CONNECT BY会遍历整棵树，而WHERE仅起到事后过滤作用。解决方案包括：1) 使用START WITH指定起点避免重复；2) 通过事前过滤数据解决数据混杂引起的内循环；3) 使用nocycle关键字和层级判断剔除循环并选择最大level的数据。

一个项目中涉及到工作流，其中一个流程是在内部人员流转，共3个环节，第一个环节是派发到项目负责人，第二个环节是项目负责人分派任务给其他人处理，第三个环节是处理人传阅给其他工作人员。

前台需要展示一个流程的流转过程，但是要求只能看自己处理环节以下的各个环节。

设想的传递路径是这样的：
这里写图片描述

--流程历史表
CREATE TABLE t_flowtask
(instid varchar2(50)   --流程编号
,taskid varchar2(50)   --环节编号
,nodeid varchar2(10)   --环节代码
,fromuser varchar2(20) --发送人
,touser   varchar2(20) --接收人
,arrivetime date default sysdate  --发送时间
);
--模拟数据
insert into t_flowtask values('201801010001',sys_guid(),'manager','','A',SYSDATE-10);
insert into t_flowtask values('201801010001',sys_guid(),'execusor','A','B',SYSDATE-9);
insert into t_flowtask values('201801010001',sys_guid(),'execusor','A','C',SYSDATE-9);
insert into t_flowtask values('201801010001',sys_guid(),'reader','B','D',SYSDATE-8);
insert into t_flowtask values('201801010001',sys_guid(),'reader','B','E',SYSDATE-8);
insert into t_flowtask values('201801010001',sys_guid(),'reader','C','F',SYSDATE-7);

刚开始开发人员写的SQL是这样的：

SQL> select RPAD(' ',2*(LEVEL-1),' ')||FROMUSER||'->'||TOUSER||'@'||TO_CHAR(ARRIVETIME,'YYYY-MM-DD') routing
  2    FROM t_flowtask
  3    WHERE instid='201801010001'
  4    CONNECT BY PRIOR TOUSER=FROMUSER
  5    ORDER SIBLINGS  BY arrivetime;
ROUTING
--------------------------------------------------------------------------------
A->B@2018-03-25
  B->D@2018-03-26
  B->E@2018-03-26
A->C@2018-03-25
  C->F@2018-03-27
B->D@2018-03-26
B->E@2018-03-26
C->F@2018-03-27
->A@2018-03-24
  A->B@2018-03-25
    B->D@2018-03-26
    B->E@2018-03-26
  A->C@2018-03-25
    C->F@2018-03-27

这里出现了很多重复数据，原因是：CONNECT BY会把整棵树以各个节点为根节点分别遍历一遍，这个问题的只要通过加START WITH就可以解决了

SQL> select RPAD(' ',2*(LEVEL-1),' ')||FROMUSER||'->'||TOUSER||'@'||TO_CHAR(ARRIVETIME,'YYYY-MM-DD') routing
  2    FROM t_flowtask
  3    WHERE instid='201801010001'
  4    START WITH FROMUSER='A'
  5    CONNECT BY PRIOR TOUSER=FROMUSER
  6    ORDER SIBLINGS  BY arrivetime;
ROUTING
--------------------------------------------------------------------------------
A->B@2018-03-25
  B->D@2018-03-26
  B->E@2018-03-26
A->C@2018-03-25
  C->F@2018-03-27

但是很快，就发现查询的数据不对，仔细看原始数据，实际上已经有多个流程的数据了，比如：
这里写图片描述

刚才SQL的结果报错了：

SQL> select RPAD(' ',2*(LEVEL-1),' ')||FROMUSER||'->'||TOUSER||'@'||TO_CHAR(ARRIVETIME,'YYYY-MM-DD') routing
  2    FROM t_flowtask
  3    WHERE instid='201801010001'
  4    START WITH FROMUSER='A'
  5    CONNECT BY PRIOR TOUSER=FROMUSER
  6    ORDER SIBLINGS  BY arrivetime;

ORA-01436: CONNECT BY loop in user data

造成这次内循环错误的原因是2个流程的数据混在一起了，在connect by语句中WHERE的作用是事后过滤而不是事前过滤。这个问题可以通过事前过滤的方式来规避。

SQL> select RPAD(' ',2*(LEVEL-1),' ')||FROMUSER||'->'||TOUSER||'@'||TO_CHAR(ARRIVETIME,'YYYY-MM-DD') routing
  2    FROM (select * from t_flowtask WHERE instid='201801010001')
  3    START WITH FROMUSER='A'
  4    CONNECT BY PRIOR TOUSER=FROMUSER
  5    ORDER SIBLINGS  BY arrivetime;
ROUTING
--------------------------------------------------------------------------------
A->B@2018-03-25
  B->D@2018-03-26
  B->E@2018-03-26
A->C@2018-03-25
  C->F@2018-03-27

过了不久，发现个别流程的查询又报内循环的错误了，分析这个流程，是这样的：
这里写图片描述
用户A即是项目负责人，又在项目中负责部分具体工作，查询的结果是：

SQL> select * from t_flowtask WHERE instid='201801010003';
INSTID                                             TASKID                                             NODEID     FROMUSER             TOUSER               ARRIVETIME
-------------------------------------------------- -------------------------------------------------- ---------- -------------------- -------------------- -----------
201801010003                                       365DC5B4C2C041E880FD1F9F47A33C6C                   execusor   A                    B                    2018/3/25 1
201801010003                                       C640E2392A8A4020939BCCDE6EBA7FB7                   execusor   A                    A                    2018/3/25 1
201801010003                                       E7EA4A332A8246169C5BC53205F2A772                   reader     B                    D                    2018/3/26 1
201801010003                                       BC1C4B9BEC5F45C6A8DBC4BFFCFE7AB4                   reader     B                    E                    2018/3/26 1
201801010003                                       425C112F018041E4AA2668C4E5A0B841                   reader     A                    F                    2018/3/27 1
201801010003                                       C98A84668E124CC29B5653691F71088C                   manager                         A                    2018/3/24 1
6 rows selected

SQL> select RPAD(' ',2*(LEVEL-1),' ')||FROMUSER||'->'||TOUSER||'@'||TO_CHAR(ARRIVETIME,'YYYY-MM-DD') routing
  2    FROM (select * from t_flowtask WHERE instid='201801010003')
  3    START WITH FROMUSER='A'
  4    CONNECT BY PRIOR TOUSER=FROMUSER
  5    ORDER SIBLINGS  BY arrivetime;

ORA-01436: CONNECT BY loop in user data

这次的问题有点棘手，如果项目还没有开始，我会要求开发人员用taskid作为上下级关联的字段，这样就不会出现loop问题了。
当然，非要从SQL层面解决，也是可以的。解决思路是这样的：

首先、用nocycle关键词避免循环；
其次、用层级关系判断（level最大）来剔除重复数据。

SQL> select routing
  2   from (
  3    select routing,arrivetime,
  4           row_number() over(partition by taskid order by lv desc) rn
  5     from (
  6      select level lv,taskid,arrivetime
  7            ,RPAD(' ',2*(LEVEL-1),' ')||FROMUSER||'->'||TOUSER||'@'||TO_CHAR(ARRIVETIME,'YYYY-MM-DD') routing
  8        FROM (select * from t_flowtask WHERE instid='201801010003')
  9        START WITH FROMUSER='A'
 10        CONNECT BY nocycle PRIOR TOUSER=FROMUSER
 11        ORDER SIBLINGS BY arrivetime
 12      )
 13    ) where rn=1
 14  order by arrivetime,routing desc;
ROUTING
--------------------------------------------------------------------------------
A->A@2018-03-25
  A->B@2018-03-25
    B->E@2018-03-26
    B->D@2018-03-26
  A->F@2018-03-27