問題描述
我在 SQL Server 中有一個 Process 表，如下所示:
I have a Process table in SQL Server like this:
workflowXML 列具有如下值:
示例 1:
<process>
<Event type="start" id="StartEvent_1">
<outgoing>SequenceFlow_0h5l5vu</outgoing>
</Event>
<Flow type="sequence"
id="SequenceFlow_0h5l5vu"
sourceRef="StartEvent_1"
targetRef="Task_1qc93ha"/>
<Flow type="sequence"
id="SequenceFlow_120gi3p"
sourceRef="Task_1qc93ha"
targetRef="Task_0x1pjee"/>
<Task type="service" id="Task_1qc93ha">
<incoming>SequenceFlow_0h5l5vu</incoming>
<outgoing>SequenceFlow_120gi3p</outgoing>
</Task>
<Task type="user" id="Task_0x1pjee">
<incoming>SequenceFlow_120gi3p</incoming>
</Task>
</process>
示例 2:
<process id="Process_1" isExecutable="false">
<Event type="start" id="StartEvent_142xowk">
<outgoing>SequenceFlow_03yocm5</outgoing>
</Event>
<Flow type="sequence"
id="SequenceFlow_03yocm5"
sourceRef="StartEvent_142xowk"
targetRef="Task_12g1q69"/>
<Task type="user" id="Task_0x1pjee">
<incoming>SequenceFlow_120gi3p</incoming>
</Task>
<Task type="user" id="Task_12g1q69">
<incoming>SequenceFlow_03yocm5</incoming>
</Task>
</process>
我想用 Flow 節點跟蹤節點流.例如，我需要查詢從開始事件(Event type=start")開始并在 Task 中結束的返回 Task 節點用戶類型(type=user").Sample1 中的此查詢返回 Task 節點，其中 id=Task_0x1pjee" 并在 Sample2 中返回 Task 節點，id=Task_12g1q69".
I want to track flow of nodes with Flow nodes. For example I need to query that return Task node that start from start event (Event type="start") and finish in Task with user type (type="user"). This query in Sample1 return Task node with id="Task_0x1pjee" and in Sample2 return Task node with id="Task_12g1q69".
我認為此查詢具有以下結構:
I think this query has this structure:
編輯 1
Sample3 具有節點，因此它們可以有多個傳入或傳出.
Sample3 has node so that they can have more than one incoming or outgoing.
<process id="Process_1" isExecutable="false">
<Event type="start" id="StartEvent_1">
<outgoing>SequenceFlow_0qn7l4p</outgoing>
</Event>
<Flow type="sequence" id="SequenceFlow_0qnhn9s" sourceRef="Task_1jfd878" targetRef="Task_15id5tl"/>
<Task type="service" id="Task_1jfd878">
<incoming>SequenceFlow_0qn7l4p</incoming>
<outgoing>SequenceFlow_0qnhn9s</outgoing>
<outgoing>SequenceFlow_10zjx6e</outgoing>
</Task>
<Flow type="sequence" id="SequenceFlow_0qn7l4p" sourceRef="StartEvent_1" targetRef="Task_1jfd878"/>
<Flow type="sequence" id="SequenceFlow_10zjx6e" sourceRef="Task_1jfd878" targetRef="Task_0qnuy6q"/>
<Task type="user" id="Task_0qnuy6q">
<incoming>SequenceFlow_10zjx6e</incoming>
<incoming>SequenceFlow_0xiah51</incoming>
</Task>
<Task type="service" id="Task_15id5tl">
<incoming>SequenceFlow_0qnhn9s</incoming>
<outgoing>SequenceFlow_0xiah51</outgoing>
</Task>
<Flow type="sequence" id="SequenceFlow_0xiah51" sourceRef="Task_15id5tl" targetRef="Task_0qnuy6q"/>
</process>
如果有人可以解釋此查詢的解決方案，將會非常有幫助.
It would be very helpful if someone could explain solution for this query.
謝謝.
推薦答案
我希望我理解正確:
您從 type="start" 開始并沿著層次結構向下走，其中 out-data 是下一個節點的 Id.此行具有未定義的深度，應在具有 type="user" 的節點處結束.
You start with type="start" and walk down a hierarchy, where the out-data is the Id of the next node. This line has an undefined depth and should end at a node with type="user".
您的第二個示例有 2 個帶有 type="user" 的任務，但只有其中一個被引用為鏈上更高節點中的輸出數據.
Your second example has got 2 Tasks with type="user", but only one of them is referenced as out-data in a higher node up the chain.
我的示例將使用額外的 EXISTS 子句過濾第二個子句.
My example will filter the second with an extra EXISTS clause.
第一個 CTE DerivedTable 包含一個您也可能使用隔離的查詢.它將以表格形式顯示完整信息.
The first CTE DerivedTable consists of a query you might use isolated too. It will bring up the full information in table-wise format.
第二個 CTE 是遞歸的，從 start 開始并向下遍歷鏈.Rank 列是鏈的順序.
The second CTE is recursive, starts with the start and traverses down the chain. The column Rank is the chain's order.
第三個 CTE 添加了反向排名，因為您似乎只對最后一項感興趣.您可能會通過 WHERE RevRank=1
The third CTE adds a reverse Rank as you seem to be interested in the last item only. You might get this by WHERE RevRank=1
DECLARE @process TABLE(ID INT IDENTITY, workflowXML XML);
INSERT INTO @process(workflowXML) VALUES
('<process>
<Event type="start" id="StartEvent_1">
<outgoing>SequenceFlow_0h5l5vu</outgoing>
</Event>
<Flow type="sequence"
id="SequenceFlow_0h5l5vu"
sourceRef="StartEvent_1"
targetRef="Task_1qc93ha"/>
<Flow type="sequence"
id="SequenceFlow_120gi3p"
sourceRef="Task_1qc93ha"
targetRef="Task_0x1pjee"/>
<Task type="service" id="Task_1qc93ha">
<incoming>SequenceFlow_0h5l5vu</incoming>
<outgoing>SequenceFlow_120gi3p</outgoing>
</Task>
<Task type="user" id="Task_0x1pjee">
<incoming>SequenceFlow_120gi3p</incoming>
</Task>
</process>')
,('<process id="Process_1" isExecutable="false">
<Event type="start" id="StartEvent_142xowk">
<outgoing>SequenceFlow_03yocm5</outgoing>
</Event>
<Flow type="sequence"
id="SequenceFlow_03yocm5"
sourceRef="StartEvent_142xowk"
targetRef="Task_12g1q69"/>
<Task type="user" id="Task_0x1pjee">
<incoming>SequenceFlow_120gi3p</incoming>
</Task>
<Task type="user" id="Task_12g1q69">
<incoming>SequenceFlow_03yocm5</incoming>
</Task>
</process>');
--這是查詢:
WITH DerivedTable AS
(
SELECT prTbl.ID AS tblID
,nd.value('local-name(.)','nvarchar(max)') AS [Name]
,nd.value('@type','nvarchar(max)') AS [Type]
,nd.value('@id','nvarchar(max)') AS Id
,COALESCE(nd.value('@sourceRef','nvarchar(max)')
,nd.value('(incoming)[1]','nvarchar(max)')) AS [In]
,COALESCE(nd.value('@targetRef','nvarchar(max)')
,nd.value('(outgoing)[1]','nvarchar(max)')) AS [Out]
FROM @process AS prTbl
CROSS APPLY prTbl.workflowXML.nodes('process') AS A(pr)
CROSS APPLY pr.nodes('*') AS B(nd)
)
,recCTE AS
(
SELECT tblID,[Name],[Type],Id,[In],[Out],1 AS [Rank]
FROM DerivedTable
WHERE [Type]='start'
UNION ALL
SELECT x.tblID,x.[Name],x.[Type],x.Id,x.[In],x.[Out],r.[Rank]+1
FROM recCTE AS r
INNER JOIN DerivedTable AS x ON x.[Id]=r.[Out]
AND EXISTS(SELECT 1
FROM DerivedTable AS y
WHERE y.tblID=x.tblID AND y.[Out]=x.[Id])
)
,ReverseRank AS
(
SELECT *
,ROW_NUMBER() OVER(PARTITION BY tblID ORDER BY [Rank] DESC) AS RevRank
FROM recCTE
)
SELECT *
FROM ReverseRank
ORDER BY tblID,[Rank]
結果(您的預期輸出為 RevRank=1):
The result (your expected output is at RevRank=1):
+-------+------+---------+-------+----------+----------------------+----------------------+----------------------+
| tblID | Rank | RevRank | Name | Type | Id | In | Out |
+-------+------+---------+-------+----------+----------------------+----------------------+----------------------+
| 1 | 1 | 5 | Event | start | StartEvent_1 | NULL | SequenceFlow_0h5l5vu |
+-------+------+---------+-------+----------+----------------------+----------------------+----------------------+
| 1 | 2 | 4 | Flow | sequence | SequenceFlow_0h5l5vu | StartEvent_1 | Task_1qc93ha |
+-------+------+---------+-------+----------+----------------------+----------------------+----------------------+
| 1 | 3 | 3 | Task | service | Task_1qc93ha | SequenceFlow_0h5l5vu | SequenceFlow_120gi3p |
+-------+------+---------+-------+----------+----------------------+----------------------+----------------------+
| 1 | 4 | 2 | Flow | sequence | SequenceFlow_120gi3p | Task_1qc93ha | Task_0x1pjee |
+-------+------+---------+-------+----------+----------------------+----------------------+----------------------+
| 1 | 5 | 1 | Task | user | Task_0x1pjee | SequenceFlow_120gi3p | NULL |
+-------+------+---------+-------+----------+----------------------+----------------------+----------------------+
| 2 | 1 | 3 | Event | start | StartEvent_142xowk | NULL | SequenceFlow_03yocm5 |
+-------+------+---------+-------+----------+----------------------+----------------------+----------------------+
| 2 | 2 | 2 | Flow | sequence | SequenceFlow_03yocm5 | StartEvent_142xowk | Task_12g1q69 |
+-------+------+---------+-------+----------+----------------------+----------------------+----------------------+
| 2 | 3 | 1 | Task | user | Task_12g1q69 | SequenceFlow_03yocm5 | NULL |
+-------+------+---------+-------+----------+----------------------+----------------------+----------------------+
更新:您的評論
我使用您評論中的 XML 測試了我的查詢:
UPDATE: Your comment
I tested my query with the XML from your comment:
INSERT INTO @process(workflowXML) VALUES
('<process>
<Event type="start" id="e1">
<outgoing>s1</outgoing>
</Event>
<Flow type="sequence" id="s1" sourceRef="e1" targetRef="t1" />
<Flow type="sequence" id="s3" sourceRef="t1" targetRef="t2" />
<Task type="user" id="t3">
<incoming>s2</incoming>
</Task>
<Task type="user" id="t1">
<incoming>s1</incoming>
<outgoing>s3</outgoing>
</Task>
<Flow type="sequence" id="s2" sourceRef="t2" targetRef="t3" />
<Task type="service" id="t2">
<incoming>s3</incoming>
<outgoing>s2</outgoing>
</Task>
</process>');
這是結果
+-------+-------+----------+----+------+------+------+---------+
| tblID | Name | Type | Id | In | Out | Rank | RevRank |
+-------+-------+----------+----+------+------+------+---------+
| 1 | Event | start | e1 | NULL | s1 | 1 | 7 |
+-------+-------+----------+----+------+------+------+---------+
| 1 | Flow | sequence | s1 | e1 | t1 | 2 | 6 |
+-------+-------+----------+----+------+------+------+---------+
| 1 | Task | user | t1 | s1 | s3 | 3 | 5 |
+-------+-------+----------+----+------+------+------+---------+
| 1 | Flow | sequence | s3 | t1 | t2 | 4 | 4 |
+-------+-------+----------+----+------+------+------+---------+
| 1 | Task | service | t2 | s3 | s2 | 5 | 3 |
+-------+-------+----------+----+------+------+------+---------+
| 1 | Flow | sequence | s2 | t2 | t3 | 6 | 2 |
+-------+-------+----------+----+------+------+------+---------+
| 1 | Task | user | t3 | s2 | NULL | 7 | 1 |
+-------+-------+----------+----+------+------+------+---------+
如果我正確理解邏輯，我的查詢就可以正常工作:
If I understand the logic correctly my query works just fine:
- 事件 id=e1 指向 s1
- 流 s1 指向 t1
- 任務 t1 指向 s3
- 流 s3 指向 t2
- 任務 t2 指向 s2
- 流 s2 指向 t3
- 任務 t3 結束
我看到的唯一不同的是，Task t1 已經是一個 type="user".如果你想要 - 在任何情況下 - 排名最高的用戶任務，你可以去掉 ReverseRank-CTE 并設置最終的
The only thing which I see differently, is the fact, that Task t1 was a type="user" already. If you want - in any case - the highest ranked user Task, you might take away the ReverseRank-CTE and set the final SELECT like
SELECT t.*
FROM recCTE AS t
WHERE t.[Rank]<=ISNULL((SELECT MIN(x.[Rank]) FROM recCTE AS x WHERE x.tblID=t.tblID AND x.[Type]='user' AND x.[Name]='Task'),999)
ORDER BY t.tblID,t.[Rank]
現在任務 t1 將是最后一個結果，因為所有后面的等級都被過濾掉了.
Now Task t1 will be the last result, as all later ranks are filtered out.
這篇關于跟蹤 SQL Server 中 XML 節點的流向的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！