简单的情况,两级且都只有一位的情况select (case when a.pid>0 then
cast(b.pid as char(1))+cast(b.id as char(1))+cast(a.id as char(1))
else
cast(a.pid as char(1))+cast(a.id as char(1))
end ) as code
,a.*
from menu a left outer join
menu b
on a.pid=b.id
order by code
cast(b.pid as char(1))+cast(b.id as char(1))+cast(a.id as char(1))
else
cast(a.pid as char(1))+cast(a.id as char(1))
end ) as code
,a.*
from menu a left outer join
menu b
on a.pid=b.id
order by code
as
declare @temp table(Menu_Id int,ordertype varchar(20))insert @temp
select Menu_Id,cast(Menu_Id as varchar(20))
from menu where Menu_ParentId =0while exists (
select a.Menu_Id,t.ordertype+cast(a.Menu_Id as varchar(20))
from menu a,@Temp t
where a.Menu_ParentId <>0
and a.Menu_ParentId=t.Menu_Id
and not exists (
select * from @temp
where Menu_Id=a.Menu_Id
))
insert @temp
select a.Menu_Id,t.ordertype+cast(a.Menu_Id as varchar(20))
from menu a,@Temp t
where a.Menu_ParentId <>0
and a.Menu_ParentId=t.Menu_Id
and not exists (
select * from @temp
where Menu_Id=a.Menu_Id
)select REPLICATE (' ',(a.Menu_Level-1)*4) +Menu_Name
from menu a,@Temp t
where a.Menu_Id=t.Menu_Id
order by ordertypego
Menu_Id int,
Menu_Name varchar(20),
Menu_ParentId int,
Menu_Level int
)
insert menu values(
1, 'name1', 0, 1)
insert menu values(
2, 'name2', 0 , 1)
insert menu values(
3, 'name3', 1 , 2)
insert menu values(
4, 'name4', 1 , 2)
insert menu values(
5, 'name5', 2 , 2)
insert menu values(
6, 'name6', 3 , 3)
insert menu values(
7, 'name7', 4 , 3)
exec ListMenu结果:
name1
name3
name6
name4
name7
name2
name5