SQL递归查询知多少

最近工作中遇到了一个问题,需要根据保存的流程数据,构建流程图。数据库中保存的流程数据是树形结构的,表结构及数据如下图:

仔细观察表结构,会发现其树形结构的特点:

  • FFIRSTNODE:标记是否为根节点
  • FSTABLENAME:标记来源单据名称
  • FSID:标记来源单据分录ID
  • FTTABLENAME :标记目标单据名称
  • FTID:标记目标单据分录ID

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

首先想到的办法就是把流程数据取回来,然后代码构造流程图。
第一个思路:根据根节点循环往下找,吭呲半天,发现没那么简单。
因为任何一个源头单据都可以多次下推目标单据:
第二个思路:先找到终极节点,在从终极节点往上找只至根节点为0。
这个思路实现起来也没有那么复杂,逻辑理清,循环遍历,最终也能实现结果。(但在大数据量情况下,易导致性能瓶颈。)

这一次我们换一个思路,让SQL来替我们做这一复杂的递归查询。

一、SqlServer 递归查询

1、基本概念

公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。公用表表达式可以包括对自身的引用,这种表达式称为递归公用表表达式。

  • 创建递归查询。有关详细信息,请参阅使用公用表表达式的递归查询
  • 在不需要常规使用视图时替换视图,也就是说,不必将定义存储在元数据中。
  • 启用按从标量嵌套 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 TBIEWHERE TBIE.FTTABLENAME = 'T_SAL_ORDERENTRY' AND TBIE.FTID = 121625UNION ALLSELECT CTBIE.FSTABLENAME,CTBIE.FSID,CTBIE.FTTABLENAME,CTBIE.FTID,CTBIE.FROUTEID  FROM T_BF_INSTANCEENTRY CTBIEINNER 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进行关联迭代不可行。

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

3、扩展:构造递归路径

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

基于上一个查询,增加一列手动拼接递归路径。注意sql中将PATH设置的类型为navarchar(4000),在union中,两边的表结构类型必须保持一致,否则会报错定位点类型和递归部分的类型不匹配。可参考此篇博文
解决CTE定位点类型和递归部分的类型不匹配

二、Oracle 递归查询

1、基本概念

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

其基本语法是:

select colname from tablenamestart with 条件1connect by 条件2where 条件3;
  • 条件1: 是根结点的限定语句,当然可以放宽限定条件,以遍历多个根结点,实际就是多棵树。
  • 条件2:是连接条件,其中用PRIOR表示上一条记录。
    比如 CONNECT BY PRIOR Id = Parent_Id就是说上一条记录的Id 是本条记录的Parent_Id
  • 条件3:过滤返回的结果集。

PRIOR关键字

运算符PRIOR被放置于等号前后的位置,决定着查询时的检索顺序。

  • PRIOR被置于CONNECT BY子句中等号的前面时,则强制从根节点到叶节点的顺序检索,为自顶向下查找。
    如: CONNECT BY PRIOR Id=Parent_Id
  • PIROR运算符被置于CONNECT BY 子句中等号的后面时,则强制从叶节点到根节点的顺序检索,为自底向上的查找。
    如:CONNECT BY Id=PRIOR Parent_Id

PS:当CONNECT BY后指定多个连接条件时,每个条件都应指定PRIOR关键字

2、动手实践

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

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

该流程为:销售订单-->发货通知单-->销售出库单-->退货通知单-->销售退货单
其中在指定连接条件时,我指定了两个条件FSID= PRIOR FTID AND FSTABLENAME =PRIOR FTTABLENAME,因为不同类型的单据各有一套自增的ID,直接用ID进行关联迭代不可行。

3、扩展:构造递归路径

Oracle中提供了SYS_CONNECT_BY_PATH函数用来进行连接路径。

SELECT TBIE.*, SUBSTR(SYS_CONNECT_BY_PATH(FTID,'->'),3)  NAME_PATH FROM T_BF_INSTANCEENTRY TBIESTART 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用来拼接递归路径。

4、显示当前节点的根节点

这个时候我们要用到connect_by_root函数,用来记录当前节点的根节点信息。

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

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 TBIESTART WITH (FTID=100501 AND FTTABLENAME = 'T_SAL_ORDERENTRY')CONNECT BY  FSID= PRIOR FTID AND FSTABLENAME =PRIOR FTTABLENAME)select * from flow_temp

为啥要讲这个呢,我们可以在oracle递归查询后进行筛选啊。

(0)

相关推荐

  • 刷题--DFS--823.排列

    DFS 823.排列 给定一个整数n,将数字1~n排成一排,将会有很多种排列方法. 现在,请你按照字典序将所有的排列方法输出. 输入格式 共一行,包含一个整数n. 输出格式 按字典序输出所有排列方案, ...

  • 【剑指Offer】反转链表

    题目描述 输入一个链表,反转链表后,输出新链表的表头. 解法1 可以使用三个辅助指针pHead, last,next pHead记录当前节点,last记录上一个节点,next记录下一个节点 首先使用n ...

  • SQL必知必会实践--mysql

    -- mysql安装 --   https://www.mysql.com/downloads/   终端执行命令   (可参考本笔记MySQL环境搭建-mysql 8.0.13 解压版安装配置方法图 ...

  • 大数据开发面试必知必会的SQL 30题!!!

    原文链接: https://blog.csdn.net/weixin_45366499/article/details/116355430 作者: 一蓑烟雨任平生 (1)查询每个区域的用户数 (2)查 ...

  • 见病知方,经方高手16张经方的用药经验

    2020年飞快的过去了!我的中医学习实践却在徘徊中慢行.翻看病案记录,最早的一个是在2月14日.也许还有更早的.遗憾的是,有些没有记录,有些没有下文.盘点自己用过的方子,算是对过去一年的小结,也算是对 ...

  • 知乎10条神回复,针针见血,看完整个人通透多了

    作者|读者来源|读者(ID:duzheweixin) 在我们的一生中, 总会遇到各种各样的问题. 有时候, 让我们苦苦追寻的人生答案, 其实就在我们的身边-- 01 问:命运是什么? 神回复:命,是弱 ...

  • 你究竟是不是“脾虚”体质?看一看嘴唇就知...

    你究竟是不是"脾虚"体质?看一看嘴唇就知道了!   中医认为"脾其华在唇",健康的唇色应该是颜色红润.均匀的,上下嘴唇的颜色无差异,也没有明显的边线. 并且整个 ...

  • 知之与结局

    对男人一知半解的女人, 最终都成了男人的妻子, 对男人无所不知的女人, 最终都成了孤身老女人.

  • 5个良心的装机必备软件,知乎超10w人推荐,错过太可惜

    新买了电脑,不知道该装什么软件? 别纠结了,今天给大家梳理了几款实用的必备软件,都是知乎上很多人推荐且热度高的产品,话不多说,上软件! 一.录制神器-ScreenToGIF ScreenToGif是一 ...

  • 心内科主任建议:中老年人一定要知道的8种...

    心内科主任建议:中老年人一定要知道的8种心脏自我诊断方法,务必学习! 1.劳力性呼吸困难. 常在中等活动量时就感到明显气短,呼吸浅表而短促,休息后可恢复. 这种呼吸困难是心脏功能不全尤其左心功能不全时 ...

  • 空腹血糖高,选什么药?餐后血糖高,选什么药?一看便知!

    糖尿病病友选药要根据自己的血糖水平,基础血糖高和餐后血糖高,或者两者均高,选药方案各不同.如果是基础血糖高,应选择降基础血糖的药物:如果是餐后血糖高,则应选择降餐后血糖的药物,而且降餐后血糖的药物不能 ...