最近工作中遇到了一個問題，需要根據保存的流程數據，構建流程圖。資料庫中保持的流程數據是樹形結構的，表結構及數據如下圖：

仔細觀察表結構，會發現其樹形結構的特點：

• 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 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、擴展：構造遞歸路徑

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用來拼接遞歸路徑。