如何对这个查询进行排序？

Question

SQL FIDDLE 示例

我有一个类似上面示例的查询，但是我不知道如何对这个查询排序，我希望结果像这样：

 Name
-----
Menu_1
-------
..... Sub_Menu_1
-------
Menu_2
-------
..... Sub_Menu_2
-------
..........Sub_Sub_Menu_2
-------
Menu_3
-------

我该怎么做？谢谢。

编辑（SQLFiddle演示中的信息）

表格：

CREATE TABLE Menus 
    (
     IdMenu int  primary key, 
     IdParentMenu varchar(20), 
     Name varchar(30)
    );

数据:

INSERT INTO Menus
(IdMenu, IdParentMenu, Name)
VALUES
('1', '0', 'Menu_1'),
('2', '0', 'Menu_2'),
('3', '0', 'Menu_3'),
('4', '1', 'SubMenu_1'),
('5', '2', 'SubMenu_2'),
('6', '5', 'Sub-SubMenu_2');

我的问题：

WITH MenuCTE (IdMenu, IdParentMenu, ParentMenu, Name, Lvl)
AS
(
    -- Anchor member
    SELECT m.IdMenu, m.IdParentMenu, CAST('' AS varchar) AS ParentMenu, m.Name, 0 AS Lvl
    FROM dbo.Menus AS m
    WHERE CAST(m.IdParentMenu AS INT) = 0
    UNION ALL

    -- Recursive member
    SELECT m.IdMenu, m.IdParentMenu, CAST(mc.Name AS varchar) AS ParentMenu, m.Name, Lvl + 1 AS Lvl
    FROM dbo.Menus AS m
    INNER JOIN MenuCTE AS mc
        ON CAST(m.IdParentMenu AS INT) = mc.IdMenu
)
SELECT IdMenu, IdParentMenu, ParentMenu, REPLICATE('....', Lvl) + Name
FROM MenuCTE
--WHERE Lvl > 0
ORDER BY IdParentMenu
GO

- Esraa_92

---

下次请包含演示中的相关信息（或任何外部链接提供的信息），这将使远程数据不可用时，其他读者仍能理解问题或答案。 - Pred

Answer 1

添加一个计算路径并按照此字段排序的字段：

WITH MenuCTE (IdMenu, IdParentMenu, ParentMenu, Path, Name, Lvl)
AS
(
    -- Anchor member
    SELECT m.IdMenu, m.IdParentMenu, 
           CAST('' AS varchar) AS ParentMenu, 
           CAST(m.Name AS varchar) AS Path, 
           m.Name AS Name, 
           0 AS Lvl
    FROM dbo.Menus AS m
    WHERE CAST(m.IdParentMenu AS INT) = 0
    UNION ALL

    -- Recursive member
    SELECT m.IdMenu, m.IdParentMenu, 
           CAST(mc.Name AS varchar) AS ParentMenu, 
           CAST(mc.Path + '.'+ m.Name AS varchar) AS Path,
           m.Name, 
           Lvl + 1 AS Lvl
    FROM dbo.Menus AS m
    INNER JOIN MenuCTE AS mc
        ON CAST(m.IdParentMenu AS INT) = mc.IdMenu
)
SELECT IdMenu, IdParentMenu, ParentMenu, 
       REPLICATE('....', Lvl) + Name, Path
FROM MenuCTE
ORDER BY Path

注意：在上面的查询中，Path 是使用 Name 字段构建的，因此菜单、子菜单等的排序是基于它们的名称进行的。 Path 的值类似于：

Menu_1
Menu_1.SubMenu_1
Menu_2
Menu_2.SubMenu_2
Menu_2.SubMenu_2.Sub-SubMenu_2

如果你想按照ID进行排序，那么只需使用IdMenu代替Name构建Path即可。在这种情况下，Path的值将会是这样的：

1
1.4
2
2.5
2.5.6

Demo here

- Giorgos Betsos

---

非常感谢您的回答，它对我帮助很大；-D - Esraa_92

Answer 2

这应该很好。

WITH MenuCTE (IdMenu, IdParentMenu, ParentMenu, Ord, Name, Lvl)
AS
(
    -- Anchor member
    SELECT m.IdMenu, m.IdParentMenu, CAST('' AS varchar) AS ParentMenu, CAST(m.IdMenu AS varchar) as Ord, m.Name, 0 AS Lvl
    FROM dbo.Menus AS m
    WHERE CAST(m.IdParentMenu AS INT) = 0
    UNION ALL

    -- Recursive member
    SELECT m.IdMenu, m.IdParentMenu, CAST(mc.Name AS varchar) AS ParentMenu, cast((mc.Ord + CAST(m.IdMenu AS varchar)) as varchar) as Ord, m.Name, Lvl + 1 AS Lvl
    FROM dbo.Menus AS m
    INNER JOIN MenuCTE AS mc
        ON CAST(m.IdParentMenu AS INT) = mc.IdMenu
)
SELECT ord, IdMenu, IdParentMenu, ParentMenu, REPLICATE('....', Lvl) + Name
FROM MenuCTE
--WHERE Lvl > 0
ORDER BY ord
GO

- Polux2