在SQL server中显示排序的层次结构行?

Assuming I have this table : ( c is a child of parent p)

假设我有这个表(c是父p的子)

Where (0 means root) — I want the order of select to be displayed as :

其中(0表示root) -我希望select的顺序显示为:

That's becuase we have 2 roots (1,40) and 1 < 40.

因为我们有两个根(1,40)和一个< 40。

So we start at 1 and then display below it - all it's descendants.

我们从1开始，然后显示在它下面——所有的都是子代。

Then we get to 40. same logic again.

然后是40。同样的逻辑。

Question:

问题:

How can I do it ?

我该怎么做呢?

I've succeeded to display it recursively + finding level of hierarchy*(not sure if it helps though)*

我已经成功地递归地显示它+查找层次结构级别*(虽然不确定它是否有用)*

WITH cte(c, p) AS (
     SELECT 40, 0 UNION ALL
     SELECT 2,3 UNION ALL
     SELECT 2,40 UNION ALL
     SELECT 3,1 UNION ALL
     SELECT 7,2 UNION ALL   
     SELECT 1,0
     ) , cte2 AS(
         SELECT c,
                p,
                PLevel = 1
         FROM   cte
         WHERE  p = 0
         UNION ALL
         SELECT cte.c,
                cte.p,
                PLevel = cte2.PLevel + 1
         FROM   cte
                INNER JOIN cte2
                     ON  cte2.c = cte.p
     )

SELECT *
FROM   cte2

Full SQL fiddle

完整的SQL小提琴

1 个解决方案

#1

You have almost done it. Just add a rank to identify each group and then sort the data on it.

你几乎做到了。只需添加一个等级来识别每个组，然后对其上的数据进行排序。

Also, as you are working with more complex hierarchy we need to change the [level] value. In is now not a number, put the full path of the current element to its parent. Where \ means parent. For example the following string:

此外，当您使用更复杂的层次结构时，我们需要更改[level]值。现在不是一个数字，把当前元素的完整路径放到父元素中。在\意味着父母。例如，以下字符串:

\1\5\4\1

1 \ \ 5 \四\ 1

represents the hierarchy below:

代表了层次结构如下:

   1
   --> 5
       --> 4
           --> 1

I get the idea from hierarchyid type. You may want to consider storing hierarchies using it, as it has handy build-in functions for working with such structures.

我是从层次id类型中得到的想法。您可能想要考虑使用它来存储层次结构，因为它有方便的内置函数来处理此类结构。

Here is full working example with the new data:

以下是新数据的完整工作示例:

DECLARE @DataSource TABLE
(
    [c] TINYINT
   ,[p] TINYINT
);

INSERT INTO @DataSource ([c], [p])
VALUES (1,0)
      ,(3, 1)
      ,(2, 3)
      ,(5,1)
      ,(7, 2)
      ,(40, 0)
      ,(2, 40);

WITH DataSource ([c], [p], [level], [rank])AS
(
    SELECT [c]
          ,[p]
          ,CAST('/' AS VARCHAR(24))
          ,ROW_NUMBER() OVER (ORDER BY [c] ASC)
    FROM  @DataSource
    WHERE [p] = 0
    UNION ALL
    SELECT DS.[c]
          ,DS.[p]
          ,CAST(DS1.[level] + CAST(DS.[c] AS VARCHAR(3)) + '/' AS VARCHAR(24))
          ,DS1.[rank]
    FROM  @DataSource DS
    INNER JOIN DataSource DS1
        ON  DS1.[c] = DS.[p]
)
SELECT [c]
      ,[p]
FROM DataSource
ORDER BY [Rank]
        ,CAST([level] AS hierarchyid);

Again, pay attention to the node (7,2) which is participating in the two groups (even in your example). I guess this is just a sample data and you have a way to defined where the node should be included.

同样，请注意参与这两个组的节点(7,2)(甚至在您的示例中)。我猜这只是一个示例数据，您可以定义应该包含节点的位置。

#1