递归SQL CTE和自定义排序顺序

您正在为线程化讨论板创建数据库架构的映像.有没有一种有效的方法为给定的线程选择正确排序的列表？我写的代码有效,但不按照我想要的方式排序.

假设你有这些数据:

ID   |  ParentID
-----------------
1    |   null
2    |   1
3    |   2
4    |   1
5    |   3

所以结构应该是这样的:

1
|- 2
|  |- 3
|  |  |- 5
|- 4

理想的情况是,在代码中,我们想要的结果集出现在以下顺序:1,2,3,5,4
问题:随着CTE我写的实际上是被返回为:1,2,4,3,5

我知道使用LINQ可以很容易地进行分组/排序,但我不愿意在内存中这样做.这似乎是目前最好的解决方案......

这是我目前正在使用的CTE:

with Replies as (   
    select c.CommentID, c.ParentCommentID 1 as Level
        from Comment c
        where ParentCommentID is null and CommentID = @ParentCommentID

    union all

    select c.CommentID, c.ParentCommentID, r.Level + 1 as Level
       from Comment c
       inner join Replies r on c.ParentCommentID = r.CommentID
)

select * from Replies

任何帮助,将不胜感激; 谢谢!

---

我是SQL的新手,之前没有听说过hierarchyid数据类型.从这篇评论中读到这篇文章后,我决定将其纳入我的设计中.如果我成功的话,今晚我会试验这个并发布更多信息.

---

使用dance2die的建议更新我的示例数据返回的结果:

ID  |  ParentID  | Level  | DenseRank
-------------------------------------
15     NULL          1         1
20     15            2         1
21     20            3         1
17     22            3         1
22     15            2         2
31     15            2         3
32     15            2         4
33     15            2         5
34     15            2         6
35     15            2         7
36     15            2         8

dance2die.. 8

我相信你一定会喜欢这个.我最近发现了Dense_Rank()函数,它用于根据MSDN "在结果集的分区内排名"

查看下面的代码以及"CommentID"的排序方式.

据我所知,您正在尝试通过ParentCommentID对结果集进行分区.

注意"denserank"栏目.

with Replies (CommentID, ParentCommentID, Level) as 
(
        select  c.CommentID, c.ParentCommentID, 1 as Level
        from    Comment c
        where   ParentCommentID is null and CommentID = 1

        union all

        select  c.CommentID, c.ParentCommentID, r.Level + 1 as Level
        from    Comment c
                inner join Replies r on c.ParentCommentID = r.CommentID
)
select  *,
        denserank = dense_rank() over (partition by ParentCommentID order by CommentID)
from    Replies
order by denserank

结果如下

1> dance2die..：

---

我相信你一定会喜欢这个.我最近发现了Dense_Rank()函数,它用于根据MSDN "在结果集的分区内排名"

查看下面的代码以及"CommentID"的排序方式.

据我所知,您正在尝试通过ParentCommentID对结果集进行分区.

注意"denserank"栏目.

with Replies (CommentID, ParentCommentID, Level) as 
(
        select  c.CommentID, c.ParentCommentID, 1 as Level
        from    Comment c
        where   ParentCommentID is null and CommentID = 1

        union all

        select  c.CommentID, c.ParentCommentID, r.Level + 1 as Level
        from    Comment c
                inner join Replies r on c.ParentCommentID = r.CommentID
)
select  *,
        denserank = dense_rank() over (partition by ParentCommentID order by CommentID)
from    Replies
order by denserank

结果如下