SQL递归查询知多少

近年来工作中相遇了3个标题,必要依据保存的流水生产线数据,营造流程图。数据库中保存的流程数据是树形结构的,表结构及数量如下图:

Oracle 1

周全观看表结构,会发现其树形结构的性情:

  • FFIENVISIONSTNODE:标记是还是不是为根节点
  • FSTABLENAME:标记来源单据名称
  • FSID:标记来源单据分录ID
  • FTTABLENAME :标记目的单据名称
  • FTID:标记目的单据分录ID

图中的流程为:
销售合同–>销售订单–>发货通告单–>销售出库单

先是想到的法子就是把流程数据取回来,然后代码构造流程图。
首先个思路:依照根节点循环往下找,吭呲半天,发现没那么简单。
因为其余1个源头单据都得以频仍下推指标单据:
第二个思路:先找到终极节点,在从巅峰节点往上找只至根节点为0。
其一思路完结起来也尚无那么复杂,逻辑理清,循环遍历,最后也能促成结果。(但在大数据量景况下,易导致质量瓶颈。)

那贰次大家换1个思路,让SQL来替大家做这一扑朔迷离的递归查询。

一 、SqlServer 递归查询

壹 、基本概念

公用表表明式 (CTE) 可以认为是在单个 SELECT、INSE奥迪Q5T、UPDATE、DELETE 或
CREATE VIEW
语句的履行范围钦点义的暂且结果集。公用表说明式能够包涵对自作者的引用,那种表明式称为递归公用表表明式。

  • 创办递归查询。有关详细新闻,请参阅动用公用表表达式的递归查询.aspx)。
  • 在不必要健康使用视图时替换视图,也正是说,不必将定义存款和储蓄在元数据中。
  • 启用按从标量嵌套 select
    语句派生的列实行分组,可能按不显眼函数或有外部访问的函数进行分组。
  • 在平等语句中反复引用生成的表。

MSDN上对CTE的介绍
T-SQL查询进阶–详解公用表表达式(CTE)

CTE 的核心语法结构如下:

WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
--只有在查询定义中为所有结果列都提供了不同的名称时,列名称列表才是可选的。
--运行 CTE 的语句为:
SELECT <column_list> FROM expression_name;

即四个部分:

  1. 公用表表明式的名字(在WITH关键字之后)
  2. 询问的列名(可选)
  3. 紧跟AS之后的SELECT语句(假若AS之后有多个对公用表的查询,则唯有首先个查询有效

2、入手实践

基于官网示例大家不会细小略就足以写出CTE语句应用于大家的使用场景:

WITH TEST_CTE 
AS
(
SELECT TBIE.FSTABLENAME,TBIE.FSID,TBIE.FTTABLENAME,TBIE.FTID,TBIE.FROUTEID FROM T_BF_INSTANCEENTRY TBIE
WHERE TBIE.FTTABLENAME = 'T_SAL_ORDERENTRY' AND TBIE.FTID = 121625
UNION ALL
SELECT CTBIE.FSTABLENAME,CTBIE.FSID,CTBIE.FTTABLENAME,CTBIE.FTID,CTBIE.FROUTEID  FROM T_BF_INSTANCEENTRY CTBIE
INNER JOIN TEST_CTE CTE ON CTBIE.FSID=CTE.FTID AND CTBIE.FSTABLENAME = CTE.FTTABLENAME
)
SELECT * FROM TEST_CTE  
--限制递归次数
OPTION(MAXRECURSION 10)

在查询中大家内定条件参数WHERE TBIE.FTTABLENAME = 'T_SAL_ORDERENTRY' AND TBIE.FTID = 121625,即可查询到内定节点的完好流程数据。
里面在与公用表TEST_CTE开始展览关联时,笔者钦点了五个标准CTBIE.FSID=CTE.FTID AND CTBIE.FSTABLENAME = CTE.FTTABLENAME,因为分歧品类的单据各有一套自增的ID,直接用ID举行关联迭代不可行。

Oracle 2

内需注意的是OPTION(MAXRECURSION 10)是用来限制递归次数,避防止无限递归导致数据库性能消耗严重。

叁 、扩张:构造递归路径

WITH TEST_CTE 
AS
(
SELECT TBIE.FSTABLENAME,TBIE.FSID,TBIE.FTTABLENAME,TBIE.FTID,TBIE.FROUTEID,Cast(TBIE.FTID as nvarchar(4000)) AS PATH
FROM T_BF_INSTANCEENTRY TBIE
WHERE TBIE.FTTABLENAME = 'T_SAL_ORDERENTRY' AND TBIE.FTID = 121625
UNION ALL
SELECT CTBIE.FSTABLENAME,CTBIE.FSID,CTBIE.FTTABLENAME,CTBIE.FTID,CTBIE.FROUTEID,CTE.PATH+'->'+Cast(CTBIE.FTID as nvarchar(4000)) PATH  
FROM T_BF_INSTANCEENTRY CTBIE
INNER JOIN TEST_CTE CTE ON CTBIE.FSID=CTE.FTID AND CTBIE.FSTABLENAME = CTE.FTTABLENAME
)
SELECT * FROM TEST_CTE  
--限制递归次数
OPTION(MAXRECURSION 10)

依照上三个询问,增添一列手动拼接递归路径。注意sql大校PATH设置的项目为navarchar(陆仟),在union中,两边的表结构类型必须保持一致,不然会报错定位点类型和递归部分的类型不匹配。可参照此篇博文
杀鸡取卵CTE定位点类型和递归部分的类型不匹配

Oracle 3

二 、Oracle 递归查询

壹 、基本概念

Oracle中的递归查询语句为start with…connect by prior,为中序遍历算法。
可参考Oracle 树操作、递归查询(select…start with…connect
by…prior)
刺探越多。

Oracle 4

其基本语法是:

select colname from tablename
start with 条件1
connect by 条件2
where 条件3;
  • 标准1:
    是根结点的限定语句,当然能够放松限制标准,以遍历多少个根结点,实际正是多棵树。
  • 原则2:是连接条件,当中用PRIOR代表上一条记下。
    比如CONNECT BY PRIOR Id = Parent_Id就是说上一条记下的Id
    是本条记下的Parent_Id
  • 条件3:过滤再次来到的结果集。

PRIOR关键字

运算符PLacrosseIO卡宴被放置于等号左右的职位,决定着询问时的探寻顺序。

  • PRIOR被置于CONNECT
    BY子句中等号的前头时,则强制从根节点到叶节点的一一检索,为自顶向下查找。
    如:CONNECT BY PRIOR Id=Parent_Id
  • PIRO昂Cora运算符被平放CONNECT BY
    子句中等号的背后时,则强制从叶节点到根节点的次第检索,为自底向上的查找。
    如:CONNECT BY Id=PRIOR Parent_Id

PS:当CONNECT BY后钦点多个连续条件时,每种条件都应钦点PRIOR关键字

贰 、动手实践

理清了用法,我们用Oracle来对查询一下业务流程。

SELECT * FROM T_BF_INSTANCEENTRY 
START WITH (FTID=100501 AND FTTABLENAME = 'T_SAL_ORDERENTRY')
CONNECT BY  FSID= PRIOR FTID AND FSTABLENAME =PRIOR FTTABLENAME

Oracle 5

该流程为:Oracle,销售订单–>发货布告单–>销售出库单–>退货公告单–>销售退货单
中间在钦赐连接条件时,作者内定了八个条件FSID= PRIOR FTID AND FSTABLENAME =PRIOR FTTABLENAME,因为不一致品种的契约各有一套自增的ID,直接用ID举行关联迭代不可行。

叁 、扩张:构造递归路径

Oracle中提供了SYS_CONNECT_BY_PATH函数用来展开延续路径。

SELECT TBIE.*, SUBSTR(SYS_CONNECT_BY_PATH(FTID,'->'),3)  NAME_PATH FROM T_BF_INSTANCEENTRY TBIE
START WITH (FTID=100501 AND FTTABLENAME = 'T_SAL_ORDERENTRY')
CONNECT BY  FSID= PRIOR FTID AND FSTABLENAME =PRIOR FTTABLENAME

据说上个查询,扩充了一列SUBSTR(SYS_CONNECT_BY_PATH(FTID,'->'),3) NAME_PATH用来拼接递归路径。

Oracle 6

肆 、展现当前节点的根节点

其目前候咱们要用到connect_by_root函数,用来记录当前节点的根节点音讯。

SELECT TBIE.*, SUBSTR(SYS_CONNECT_BY_PATH(FTID,'->'),3)NAME_PATH, (connect_by_root FTID) ROOT  FROM T_BF_INSTANCEENTRY TBIE
START WITH (FTID=100501 AND FTTABLENAME = 'T_SAL_ORDERENTRY')
CONNECT BY  FSID= PRIOR FTID AND FSTABLENAME =PRIOR FTTABLENAME

Oracle 7

5、Oracle中的with…as语句

Oracle也有with..as 查询语法,一般用来进行子查询,进步查询成效。
语法:

with tempTableName as ( select * from table1 )
select * from tempTableName

拿我们的案例举例正是:

with flow_temp as (
SELECT TBIE.*, SUBSTR(SYS_CONNECT_BY_PATH(FTID,'->'),3)NAME_PATH, (connect_by_root FTID) ROOT  FROM T_BF_INSTANCEENTRY TBIE
START WITH (FTID=100501 AND FTTABLENAME = 'T_SAL_ORDERENTRY')
CONNECT BY  FSID= PRIOR FTID AND FSTABLENAME =PRIOR FTTABLENAME
)

select * from flow_temp

干什么要讲那个吧,大家能够在oracle递归查询后展开筛选啊。

相关文章