tsql - 带有 order by 的递归 CTE

Question

我正在处理分层数据并使用递归 CTE 列出如下项目:

Eletronics
    Televisions
        Tube
        LCD
        Plasma
    Portable Electronic
        MP3 Players
            Flash
        CD Player
        Two Way Radios

我的问题是:
如何按标题排序并尊重层次结构？

像这样:

Eletronics
    Portable Electronic
        CD Player
        MP3 Players
            Flash
        Two Way Radios
    Televisions
        LCD
        Plasma
        Tube

塔克斯

Answer 1

这是正确的 CTE(仅适用于 asc)

;WITH CTE AS
(
SELECT  id, id_parent, name_product
        ,HierarchicalPath = CAST('\'+CAST(name_product AS VARCHAR(MAX)) AS VARCHAR(MAX)) 
FROM @tab where id_parent is null -- Starts with the first level

UNION ALL

SELECT  t.id, t.id_parent, t.name_product
        ,HierarchicalPath = CAST(c.HierarchicalPath + '\'+CAST(t.name_product AS VARCHAR(MAX)) AS VARCHAR(MAX))
FROM @tab t INNER JOIN CTE C
ON t.id_parent = C.id
)

select * from cte order by HierarchicalPath