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;
即三个部分:
- 公用表表达式的名字(在WITH关键字之后)
- 查询的列名(可选)
- 紧跟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进行关联迭代不可行。
需要注意的是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 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(4000),在union中,两边的表结构类型必须保持一致,否则会报错定位点类型和递归部分的类型不匹配
。可参考此篇博文
解决CTE定位点类型和递归部分的类型不匹配。
二、Oracle 递归查询
1、基本概念
Oracle中的递归查询语句为start with…connect by prior
,为中序遍历算法。
可参考Oracle 树操作、递归查询(select…start with…connect by…prior)了解更多。
其基本语法是:
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关键字
运算符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 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
用来拼接递归路径。
4、显示当前节点的根节点
这个时候我们要用到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
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递归查询后进行筛选啊。
推荐链接:你必须知道的ML.NET开发指南
推荐链接:你必须知道的Office开发指南
推荐链接:你必须知道的IOT开发指南
推荐链接:你必须知道的Azure基础知识
推荐链接:你必须知道的PowerBI基础知识
![](https://files.cnblogs.com/files/sheng-jie/scan-follow.bmp)
关注我的公众号『微服务知多少』,我们微信不见不散。
阅罢此文,如果您觉得本文不错并有所收获,请【打赏】或【推荐】,也可【评论】留下您的问题或建议与我交流。 你的支持是我不断创作和分享的不竭动力!