SQL Server 父/子 CTE 排序问题

我需要创建一个查询，该查询返回父注释的记录集，其子注释列在每个父注释下方，按父(根)注释 ID 排序.

例如，这是我想看到的输出:

我使用了 函数返回 IS 的第一个元素NOT NULL，但您使用 0 而不是 NULL 来检查元素是否为父元素.

在提供的 StackOverflow 链接中，用户使用 NULL' 来执行此操作.因此，您必须更改ORDER BY` 子句中的条件逻辑用户.

正如 marc_S 在他的评论中指出的那样，如果您不使用 SQL Server 2012，则可以将 IIF 语句替换为 CASE WHEN 块.

I need to create a query that returns a record set of parent notes, with their child notes listed beneath each parent, ordered by the parent (root) note id.

For example, here is the output I would like to see:

I used the accepted solution from this page, which was this:

SELECT ID, ParentID, Datestamp
FROM ForumMessages
ORDER BY coalesce(ParentID, ID), Datestamp

My resultant query was:

SELECT NoteID, ParentNoteID, NoteText
FROM dms_Notes
WHERE DocketID = 43477 -- this filter is just to make the resultset smaller
ORDER BY Coalesce(NoteID, ParentNoteID), NoteText

Which resulted in the output below. As you can see, the last record with id 23478 is not listed below the parent 23471

I also tried switching the ORDER BY around to ParentNoteID, NoteID, but the result was even further off:

What is wrong and why is this not working? I have tried to base it exactly on the accepted solution, but it just doesn't appear to work properly? Thanks.

解决方案

Could you try:

SELECT NoteID, ParentNoteID, NoteText
FROM dms_Notes
WHERE DocketID = 43477 -- this filter is just to make the resultset smaller
ORDER BY IIF(ParentNoteID <> 0, ParentNoteID, NoteID), NoteText

The COALESCE functions returns the first element that IS NOT NULL, but you are using 0 instead NULL to check if element is parent.

In the providing StackOverflow link, the user is using NULL' to do this. Because of this you have to change the condition logic user in theORDER BY` clause.

EDIT:

As marc_S point in his comment, if you are not using SQL Server 2012, you can replace the IIF statement with CASE WHEN block.