重新审视 SQL Server 父/子 CTE 排序问题 [英] SQL Server Parent/Child CTE Ordering issue revisited

问题描述

昨天，我发布了

Yesterday, I posted this issue regarding Parent/Child ordering that I thought had been resolved.

The resultant query that worked for a while was:

SELECT NoteID, ParentNoteID, NoteText
FROM dms_Notes
WHERE DocketID = 43477
ORDER BY CASE WHEN ParentNoteID = 0 THEN NoteID ELSE ParentNoteID END

As you can see in the output below, it worked well. But then later on it went awry after adding more children records after NoteID 23480.

As you can see, the children after that, that have a ParentNoteID of 23482 don't seem to be listed under the relevant parent Note, bu rather at the end.

Why is this? Thanks

UPDATE: Here is another simpler example. As you can see, the child for the second parent (23499) doesn't sit under the parent. So is it correct to say that the previous solution provided doesn't actually work properly?

解决方案

What you can do is use a Revursive CTE to find the RootId which you can then use in the ORDER BY

;WITH c AS 
(
    SELECT NoteId, NoteId ParentNoteId, NoteId AS RootId, t.NoteText
    FROM tbl 
    WHERE ParentNoteId = 0 

    UNION ALL 

    SELECT t.NoteId, t.ParentNoteId, c.RootId, t.NoteText
    FROM tbl AS t 
    INNER JOIN c ON t.ParentNoteId = c.NoteId 
    WHERE t.NoteId <> 0
) 
SELECT 
    c.NoteId, 
    CASE WHEN c.ParentNoteId = c.NoteId THEN 0 ELSE c.ParentNoteId END AS ParentId, 
    c.NoteText
FROM c
ORDER BY RootId, ParentNoteId, NoteId

Which give a result like (text ommitted)

NoteId  ParentId
--------------------
23471   0
23472   23471
23473   23471
23478   23471
23481   23471
23474   23472
23475   23474
23476   0
23477   23476
23482   23476
23484   23482
23485   23482
23486   23482
23487   23482
23480   0

demo

这篇关于重新审视 SQL Server 父/子 CTE 排序问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！