关于菜单的一条T-sql

[ran丶]

你好，现在要求如下，菜单Menu表
查询出来数据
父级一行，下面全是子集，
即：
父级
子集
子集
父级
子集
子集
整个Menu

[shoppo0505]

Sql没办法显示这种树结构，只能显示相关信息，树结构要在程序用实现。 没给出表格结构，帮不了你。

[中国风]

生成一列用于排序ord

;WITH BTree
AS
(
SELECT *,ord=CAST(RIGHT(10000+ROW_NUMBER()OVER(ORDER BY ID),4) AS VARCHAR(50)) FROM Menu WHERE ParentID=0--父级=0
UNION ALL
SELECT a.*,ord=CAST(a.ord+RIGHT(10000+ROW_NUMBER()OVER(PARTITION BY a.ParentID ORDER BY ID),4) AS VARCHAR(50)) FROM Menu AS a INNER JOIN BTree AS b ON a.ParentID=b.ID
)
SELECT * FROM BTree ORDER BY ord

[中国风]

改改加上空格和CAST(b.ord

;WITH BTree
AS
(
SELECT *,ord=CAST(RIGHT(10000+ROW_NUMBER()OVER(ORDER BY ID),4) AS VARCHAR(50)),lev=0 FROM Menu WHERE ParentID=0--父级=0
UNION ALL
SELECT a.*,ord=CAST(b.ord+RIGHT(10000+ROW_NUMBER()OVER(PARTITION BY a.ParentID ORDER BY a.ID),4) AS VARCHAR(50)),lev=b.lev+1 FROM Menu AS a INNER JOIN BTree AS b ON a.ParentID=b.ID
)
SELECT ID,ParendID,SPACE(lev)+Name FROM BTree ORDER BY ord