表1
id reid Name
1 0 菜单1
2 1 菜单2
3 0 菜单3
3 1 菜单4
如果直接查询 是1 2 3 4
我想把菜单1下面的 归到菜单1下面
例如 1 2 4 然后再 3
递归排序下，google下代码很多
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#1') is null
drop table #1
Go
Create table #1([id] int,[reid] int,[Name] nvarchar(23))
Insert #1
select 1,0,N'菜单1' union all
select 2,1,N'菜单2' union all
select 3,0,N'菜单3' union all
select 4,1,N'菜单4'
GO
;WITH Cte
AS
(
Select ,ord=CAST(RIGHT(1000+id,3) AS VARCHAR(50)) from #1 WHERE reid=0
UNION ALL
SELECT a.,ord=CAST(b.ord+RIGHT(1000+a.id,3)AS VARCHAR(50)) FROM #1 AS a INNER JOIN Cte AS b ON b.ID=a.reid
)
SELECT [id],[reid],[Name] FROM Cte ORDER BY ord
/*
id reid Name
1 0 菜单1
2 1 菜单2
4 1 菜单4
3 0 菜单3
*/
在此贴已回复
http://bbs.csdn.net/topics/391957553