我正在尝试创建一个论坛设置,您可以在其中对特定帖子发表评论,并将回复按日期顺序显示在父项的正下方。父级下面只有一个子级别。这是示例数据集:
ID.ParentID.Datestamp 12.NULL.2013-03-01 1:00pm 13.NULL.2013-03-01 2:00pm 14.12.2013-03-01 2:20pm 15.12.2013-03-01 2:30pm 16.NULL.2013-03-01 3:30pm
这就是我想要最终得到的:
12.NULL.2013-03-01 1:00pm 14.12.2013-03-01 2:20pm 15.12.2013-03-01 2:30pm 13.NULL.2013-03-01 2:00pm 16.NULL.2013-03-01 3:30pm
我知道我需要某种CTE,但这不会在适当的父项下对子项进行排序(显然,因为没有ORDER BY子句);我不知道正确的顺序。谁能提供一些见识?
; WITH Messages AS ( SELECT ID, ParentID, Datestamp FROM ForumMessages WHERE ParentID IS NULL -- Recursive UNION ALL SELECT t2.ID, t2.ParentID, t2.Datestamp FROM ForumMessages AS t2 JOIN Messages AS m ON t2.ParentID = m.ID ) SELECT ID, ParentID, Datestamp FROM Messages
对于单个级别的深度,您无需使用递归-尝试:
SELECT ID, ParentID, Datestamp FROM ForumMessages order by coalesce(ParentID,ID), Datestamp