如何通过层次结构对行进行排序

Question

我有一张包含层级关系的父子节点的表格，希望按照它们之间的层级顺序排序。表格如下：

id|parent|type
--------------
1 |0     |1
2 |0     |1
3 |0     |1
4 |0     |2
5 |0     |2
6 |2     |2
7 |3     |2

作为结果，我希望得到这个：

id|parent|type
--------------
1 |0     |1
2 |0     |1
6 |2     |2
3 |0     |1
7 |3     |2
4 |0     |2
5 |0     |2

我希望获得类似树形视图的东西，其中类型1排在前面，类型2排在最后。

现在我正在尝试使用递归，但顺序是错误的：

with cte as
(
  select id, parent, type from tbl where id=1
  union all
  select id, parent, type,
  ROW_NUMBER()over(
   order by
         (case when t.type = 1 then 1
            when t.type = 2 then 2
    else 1000
    end) as rn
  from tbl t
  inner join cte c on c.id=t.parent
)
select * from cte
order by rn

我该怎么做？

- Gleb

---

如果您想要创建类似树形结构的表示，Connect By 是您最好的朋友。http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm - istovatis

@istovatis 非常酷，但我正在使用 MS Sql server。 - Gleb

你能解释一下 type = 2 如何解释4和5这样的值吗？为什么你把它们放在6之后，而不是之前？而且它们为什么有这个 type 值呢，首先... - Roger Wolf

你能发一下你的 fiddle 吗？ - A_Sk

@RogerWolf 我有对象类型（type1，type2等），type2（和3）始终是type1的子级，但是type1的对象也可能有type1的子级。就像文件夹和文件一样。 - Gleb

Answer 1

可以使用以下递归CTE来完成：

WITH cte AS (
  SELECT *,
    CAST(ROW_NUMBER() OVER(ORDER BY id) AS REAL) rn,
    1 level
  FROM tbl
  WHERE parent = 0
  UNION ALL
  SELECT t2.*,
    cte.rn + (CAST(ROW_NUMBER() OVER(ORDER BY t2.id) AS REAL) / POWER(10, cte.level)) rn,
    cte.level + 1 level
  FROM tbl t2 INNER JOIN cte
    ON t2.parent = cte.id
)
SELECT id, parent, type
FROM cte
ORDER BY rn

请参阅 SQLFiddle，其中包含更复杂的样本数据（更深层次的层次结构，“无序父子ID”）。

- Amit

---

除非数据始终如此简单，否则将失败。例如，超过1个嵌套层级将使其失效。 - Roger Wolf

那很好，但是有一个细节，ID可能会比父ID大。 - Gleb

@Gleb - 修改了答案，适用于所有更深层次的层级结构，“无序的父子ID”，请参见fiddle。 - Amit

Answer 2

使用带有CTE的order by hierarchyid是简单的，不需要测试递归关系。

DECLARE @Data table (Id int identity(1,1) primary key, Parent int, Type int)

INSERT @Data VALUES 
(0, 1),
(0, 1),
(0, 1),
(0, 2),
(0, 2),
(2, 2),
(3, 2)

SELECT * FROM @Data

;WITH level AS
(
    -- The root, build the hierarchy by /{Type}.{Id}/, where Type is important then Id
    SELECT *, -- 0 AS Level,
        '/' + CONVERT(varchar(max), Type + 0.1 * Id) + '/' AS Ordering 
    FROM @Data 
    WHERE Parent = 0
    UNION ALL
    -- Connect the parent with appending the hierarchy
    SELECT d.*, -- c.Level + 1, 
        c.Ordering + CONVERT(varchar(max), d.Type + 0.1 * d.Id) + '/' 
    FROM @Data d INNER JOIN level c ON d.Parent = c.Id
)
SELECT Id, Parent, Type FROM level 
ORDER BY CAST(Ordering as hierarchyid) -- The key part to convert data type

SQL Fiddle

- Eric

---

这几乎是我需要的，谢谢。但还有一个问题，现在层次结构是正确的，但类型3和类型4的对象位于类型1和类型2的对象之上。而且类型1和类型2的id比3和类型4的id小。我该怎么解决这个问题？ - Gleb

@ Gleb很高兴能有更多数据来测试您的问题。 - Eric

通过乘法解决了这个问题。（当v.type='typ1'时，结果为-0.1；当v.type='type2'时，结果为-0.1；否则结果为0.1）* r.id) + '/'。谢谢。 - Gleb

Answer 3

with cte as
(
  select *, 1 level, row_number() over (order by id) rn
  from tbl
  where parent = 0
  union all
  select t.*, cte.level + 1 level, cte.rn
  from cte
  inner join tbl t on cte.id = t.parent
)
select id, parent, type from cte order by rn, level

- daniel