我有一个类似上面示例的查询,但是我不知道如何对这个查询排序,我希望结果像这样:
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