久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

    <small id='yF0hA'></small><noframes id='yF0hA'>

        <legend id='yF0hA'><style id='yF0hA'><dir id='yF0hA'><q id='yF0hA'></q></dir></style></legend>
          <bdo id='yF0hA'></bdo><ul id='yF0hA'></ul>
        <tfoot id='yF0hA'></tfoot>
      1. <i id='yF0hA'><tr id='yF0hA'><dt id='yF0hA'><q id='yF0hA'><span id='yF0hA'><b id='yF0hA'><form id='yF0hA'><ins id='yF0hA'></ins><ul id='yF0hA'></ul><sub id='yF0hA'></sub></form><legend id='yF0hA'></legend><bdo id='yF0hA'><pre id='yF0hA'><center id='yF0hA'></center></pre></bdo></b><th id='yF0hA'></th></span></q></dt></tr></i><div class="qwawimqqmiuu" id='yF0hA'><tfoot id='yF0hA'></tfoot><dl id='yF0hA'><fieldset id='yF0hA'></fieldset></dl></div>

      2. 跟蹤 SQL Server 中 XML 節(jié)點的流向

        Tracking the flow of XML nodes in SQL Server(跟蹤 SQL Server 中 XML 節(jié)點的流向)

        <tfoot id='zR0jg'></tfoot>
            <tbody id='zR0jg'></tbody>
          <i id='zR0jg'><tr id='zR0jg'><dt id='zR0jg'><q id='zR0jg'><span id='zR0jg'><b id='zR0jg'><form id='zR0jg'><ins id='zR0jg'></ins><ul id='zR0jg'></ul><sub id='zR0jg'></sub></form><legend id='zR0jg'></legend><bdo id='zR0jg'><pre id='zR0jg'><center id='zR0jg'></center></pre></bdo></b><th id='zR0jg'></th></span></q></dt></tr></i><div class="qwawimqqmiuu" id='zR0jg'><tfoot id='zR0jg'></tfoot><dl id='zR0jg'><fieldset id='zR0jg'></fieldset></dl></div>
          • <bdo id='zR0jg'></bdo><ul id='zR0jg'></ul>

            <small id='zR0jg'></small><noframes id='zR0jg'>

                  <legend id='zR0jg'><style id='zR0jg'><dir id='zR0jg'><q id='zR0jg'></q></dir></style></legend>
                • 本文介紹了跟蹤 SQL Server 中 XML 節(jié)點的流向的處理方法,對大家解決問題具有一定的參考價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)吧!

                  問題描述

                  限時送ChatGPT賬號..

                  我在 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 節(jié)點跟蹤節(jié)點流.例如,我需要查詢從開始事件(Event type=start")開始并在 Task 中結(jié)束的返回 Task 節(jié)點用戶類型(type=user").Sample1 中的此查詢返回 Task 節(jié)點,其中 id=Task_0x1pjee" 并在 Sample2 中返回 Task 節(jié)點,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".

                  我認為此查詢具有以下結(jié)構(gòu):

                  I think this query has this structure:

                  編輯 1

                  Sample3 具有節(jié)點,因此它們可以有多個傳入或傳出.

                  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" 開始并沿著層次結(jié)構(gòu)向下走,其中 out-data 是下一個節(jié)點的 Id.此行具有未定義的深度,應(yīng)在具有 type="user" 的節(jié)點處結(jié)束.

                  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" 的任務(wù),但只有其中一個被引用為鏈上更高節(jié)點中的輸出數(shù)據(jù).

                  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]
                  

                  結(jié)果(您的預(yù)期輸出為 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>');
                  

                  這是結(jié)果

                  +-------+-------+----------+----+------+------+------+---------+
                  | 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
                  • 任務(wù) t1 指向 s3
                  • 流 s3 指向 t2
                  • 任務(wù) t2 指向 s2
                  • 流 s2 指向 t3
                  • 任務(wù) t3 結(jié)束

                  我看到的唯一不同的是,Task t1 已經(jīng)是一個 type="user".如果你想要 - 在任何情況下 - 排名最高的用戶任務(wù),你可以去掉 ReverseRank-CTE 并設(shè)置最終的 SELECT 像

                  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]
                  

                  現(xiàn)在任務(wù) t1 將是最后一個結(jié)果,因為所有后面的等級都被過濾掉了.

                  Now Task t1 will be the last result, as all later ranks are filtered out.

                  這篇關(guān)于跟蹤 SQL Server 中 XML 節(jié)點的流向的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網(wǎng)!

                  【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題,如果有圖片或者內(nèi)容侵犯了您的權(quán)益,請聯(lián)系我們刪除處理,感謝您的支持!

                  相關(guān)文檔推薦

                  SQL query to get all products, categories and meta data woocommerce/wordpress(獲取所有產(chǎn)品、類別和元數(shù)據(jù)的 SQL 查詢 woocommerce/wordpress)
                  Can I figure out a list of databases and the space used by SQL Server instances without writing SQL queries?(我可以在不編寫 SQL 查詢的情況下找出數(shù)據(jù)庫列表和 SQL Server 實例使用的空間嗎?) - IT屋-程序員軟件開發(fā)
                  How to create a login to a SQL Server instance?(如何創(chuàng)建對 SQL Server 實例的登錄?)
                  How to know the version and edition of SQL Server through registry search(如何通過注冊表搜索知道SQL Server的版本和版本)
                  Why do I get a quot;data type conversion errorquot; with ExecuteNonQuery()?(為什么會出現(xiàn)“數(shù)據(jù)類型轉(zhuǎn)換錯誤?使用 ExecuteNonQuery()?)
                  How to show an image from a DataGridView to a PictureBox?(如何將 DataGridView 中的圖像顯示到 PictureBox?)
                  <tfoot id='jnw2Q'></tfoot>

                      <legend id='jnw2Q'><style id='jnw2Q'><dir id='jnw2Q'><q id='jnw2Q'></q></dir></style></legend>
                        <tbody id='jnw2Q'></tbody>

                          <bdo id='jnw2Q'></bdo><ul id='jnw2Q'></ul>
                          <i id='jnw2Q'><tr id='jnw2Q'><dt id='jnw2Q'><q id='jnw2Q'><span id='jnw2Q'><b id='jnw2Q'><form id='jnw2Q'><ins id='jnw2Q'></ins><ul id='jnw2Q'></ul><sub id='jnw2Q'></sub></form><legend id='jnw2Q'></legend><bdo id='jnw2Q'><pre id='jnw2Q'><center id='jnw2Q'></center></pre></bdo></b><th id='jnw2Q'></th></span></q></dt></tr></i><div class="qwawimqqmiuu" id='jnw2Q'><tfoot id='jnw2Q'></tfoot><dl id='jnw2Q'><fieldset id='jnw2Q'></fieldset></dl></div>

                          <small id='jnw2Q'></small><noframes id='jnw2Q'>

                          • 主站蜘蛛池模板: 国产性网| 色欧美片视频在线观看 | 欧美精品久久久 | 国产三级精品三级在线观看四季网 | 日韩aⅴ片 | av在线亚洲天堂 | 成人av资源在线 | 成人国产精品久久久 | 精品视频一区二区在线观看 | 91精品国产91久久综合桃花 | 日韩视频中文字幕 | 天堂av免费观看 | 精品在线一区二区三区 | 在线天堂免费中文字幕视频 | 黄色片在线网站 | 自拍偷拍小视频 | 狠狠久| 精品国产一区二区三区久久 | 中文字幕精品一区二区三区精品 | 精品久久成人 | 亚洲欧洲一区 | 日韩精品一区二区三区视频播放 | 成人精品区 | 人妖无码 | av免费网站在线观看 | 一区二区三区欧美在线 | av色站 | 亚洲 中文 欧美 日韩 在线观看 | 欧美激情区 | 久久久久久久久久久丰满 | 亚洲一区在线播放 | 九色视频网站 | 久久国产婷婷国产香蕉 | 久久久精品一区 | 夜夜摸天天操 | 成人激情视频免费观看 | 久久综合狠狠综合久久综合88 | 欧美a在线 | 精品视频在线观看 | 午夜影院在线观看免费 | 国产精品区二区三区日本 |