SQL递归查询知多少

xian123

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

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

• 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。
  
  
• 条件3：过滤返回的结果集。
  

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

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

　　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递归查询后进行筛选啊。