在递归CTE下控制同级顺序？ [英] Controlling the sibling order under recursive CTE?

问题描述

我有一个CTE查询，查找主要叶子和子叶子。但是我无法控制两个兄弟姐妹之间的叶子选择顺序：

表中的每一行都声明为：

（childID INT，parentID INT，NAME NVARCHAR（30），location int）

在位置优先排序的地方，他们是兄弟姐妹。

所以我有这个树结构：这些对具有位置优先级：

例如：

 ʻa` （location = 1）应该在`f`（location = 2）
`b`（location = 1）应该在`e`（location = 2）
`d`（location = 1 ）应该在**之前**（位置= 2）
  

问题是看来我必须首先 排序 childID ，才能看到正确的结构（ sibling unsorted ）。

但是-什么我的排序方式应该看起来如何，这样我就能看到正确的结构（&&兄弟姐妹排序的）？

（在我的示例中： d 应该在<$之前c $ c> c ）

> 这是可以生成所有树叶（未排序的兄弟姐妹）的有效查询

ps childID 不表示有关排序的任何信息。它只是一个占位符。就像我说的那样，两个兄弟之间的位置是通过 location 列进行的。（在这里，childId被排序是因为那是我插入行的顺序...

解决方案

您可以在CTE中计算树节点的路径并将其用于排序

 ;通过CTE AS（
 SELECT childID，parentID，0 AS depth，NAME，location，
 cast（location as varbinary（max））path 
 FROM @myTable 
 WHERE childID = parentID 
 UNION ALL 
选择TBL.childID，TBL.parentID，
 CTE.depth + 1，TBL.name，TBL.location， 
 cte.path + cast（TBL.location为binary（4））
来自@myTable AS TBL 
在TBL上内部加入CTE.parentID = CTE.childID 
在哪里TBL。 childID<> TBL.parentID 
）
选择深度，childID，parentID，位置，REPLICATE（'----'，depth）+名称
 FROM CTE 
 ORDER BY路径
  

I have a CTE query which looks for main leafs and sub leafs. but I'm having trouble controling the leaf selection order between 2 siblings :

Each row in the table is declared as :

(childID INT ,parentID INT ,NAME NVARCHAR(30),location int)

Where location is a priority to sort IFF they are siblings.

And so I have this tree structure : those pairs has a location priority :

For example :

`a` ( location=1) should be before `f` (location=2)
`b` ( location=1) should be before `e` (location=2)
`d` ( location=1) should be **before** `c` (location=2)

The problem is that it seems that I must first order by childID in order to see the right structure ( sibling unsorted).

But - what how does my order by should look like so I will be able to see the right structure (&& sibling sorted) ?

(in my example : d should come before c)

Here is the working query which yields all the tree leafs ( unsorted siblings)

p.s. childID is not indicating anything about the sorting. it's just a placeholder. as I said , the location between 2 brothers is by the location column.( here , childId is sorted because thats the order of which i inserted the rows...

解决方案

You can calculate path of the tree node in your CTE and use it for sorting

;WITH CTE AS(
   SELECT childID, parentID, 0 AS depth, NAME , location,
         cast(location as varbinary(max)) path
   FROM   @myTable
   WHERE   childID = parentID 
    UNION ALL
    SELECT  TBL.childID, TBL.parentID,
           CTE.depth + 1 , TBL.name ,TBL.location,
           cte.path + cast(TBL.location as binary(4))
    FROM   @myTable AS TBL
            INNER JOIN CTE  ON  TBL.parentID = CTE.childID
    WHERE   TBL.childID<>TBL.parentID
)
SELECT depth, childID, parentID, location, REPLICATE('----', depth) + name
FROM CTE
ORDER BY path

这篇关于在递归CTE下控制同级顺序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！