sql - 平面数据层级顺序

你知道要把这个平面数据按以下顺序订购吗？
这个简单的查询不起作用

select QuestionID , QuestionText , ReferenceID , ParentQuestionID from Question
where ReferenceID = 10208
order by ParentQuestionID,QuestionID

平面数据

这应该是这样的命令

最佳答案

您可以构建层次结构字符串，然后对其进行排序。对于存储，您可以使用hierarchyid

WITH Question AS
(
    SELECT * FROM (VALUES
        (10208, -1),
        (10209, 10208),
        (10211, 10208),
        (10214, 10208),
        (10212, 10209),
        (10213, 10209),
        (10215, 10212)
    ) Question(QuestionID, ParentQuestionID)
), cte AS
(
    -- Anchor
    SELECT *, 0 AS Level, CAST('/' + CAST(QuestionID AS varchar) + '/' AS varchar(100)) AS Hierarchy
    FROM Question WHERE ParentQuestionID = -1
    UNION ALL
    -- Recursive part
    SELECT q.*, Level + 1, CAST(Hierarchy + CAST(q.QuestionID AS varchar) + '/' AS varchar(100))
    FROM Question q INNER JOIN cte ON q.ParentQuestionID = cte.QuestionID
)
SELECT * FROM cte ORDER BY Hierarchy

SQL Fiddle
编辑
按降序排列

WITH Question AS
(
    SELECT * FROM (VALUES
        (10208, -1),
        (10209, 10208),
        (10211, 10208),
        (10214, 10208),
        (10212, 10209),
        (10213, 10209),
        (10215, 10212)
    ) Question(QuestionID, ParentQuestionID)
), cte AS
(
    -- Anchor
    SELECT *, 0 AS Level, CAST('/' + CAST(QuestionID AS varchar(max)) + '/' AS varchar(max)) AS Hierarchy
    FROM Question WHERE ParentQuestionID = -1
    UNION ALL
    -- Recursive part
    SELECT q.*, Level + 1, CAST(cte.Hierarchy + CAST(q.QuestionID AS varchar(max)) + '/' AS varchar(max))
    FROM Question q INNER JOIN cte ON q.ParentQuestionID = cte.QuestionID
), rn AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ParentQuestionID ORDER BY Hierarchy DESC) Seq FROM cte
)
SELECT * FROM rn ORDER BY ParentQuestionID, Seq

关于sql - 平面数据层级顺序，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33992095/