SQL父/子CTE排序 [英] SQL Parent/Child CTE Ordering
问题描述
我正在尝试创建一个论坛设置,您可以在其中对特定帖子发表评论,并使回复按日期顺序显示在父级下方。父级下面只有一个子级别。这是示例数据集:
I'm trying to create a forum setup where you can comment on specific posts and have the replies show immediately below the parent in date order. There will only be one sub-level below the parent. Here's the example dataset:
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
这就是我要结束的事情:
And here's what I'm trying to end up with:
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子句);我找不到正确的顺序。谁能提供一些见识?
I know I need some sort of CTE going, but this doesn't order the children under the appropriate parent (obviously, since there's no ORDER BY clause); I couldn't figure out the proper ordering. Can anyone provide some insight?
; 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
推荐答案
对于单个级别的深度,您无需使用递归-尝试:
For a single level depth, you don't need to use recursion - try:
SELECT ID, ParentID, Datestamp
FROM ForumMessages
order by coalesce(ParentID,ID), Datestamp
这篇关于SQL父/子CTE排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!