SQL——递归
0
悬赏园豆：10
[已关闭问题]
关闭于 2011-12-16 08:19
create table MenuInfo
(
MenuId int not null primary key identity(1,1) , -- 菜单编号 主键 自动增长
MenuName nvarchar(50) null, -- 菜单名称
CreateTime datetime null, -- 创建时间
URL nvarchar(50) null, -- 菜单路径
Remark nvarchar(200) null,
MenuParentId int not null --父级菜单编号
)
(
MenuId int not null primary key identity(1,1) , -- 菜单编号 主键 自动增长
MenuName nvarchar(50) null, -- 菜单名称
CreateTime datetime null, -- 创建时间
URL nvarchar(50) null, -- 菜单路径
Remark nvarchar(200) null,
MenuParentId int not null --父级菜单编号
)
insert into MenuInfo values('考试管理',getdate(),'ExamCenter/Index','考试中心',0)
insert into MenuInfo values('考试子项1',getdate(),'ExamCenter/children1','考试中心',1)
insert into MenuInfo values('考试子项2',getdate(),'ExamCenter/children2','考试中心',1)
insert into MenuInfo values('考试孙项1',getdate(),'ExamCenter/Nexchildren1','考试中心',2)
insert into MenuInfo values('考试孙项2',getdate(),'ExamCenter/Nexchildren2','考试中心',2)
insert into MenuInfo values('考试子项1',getdate(),'ExamCenter/children1','考试中心',1)
insert into MenuInfo values('考试子项2',getdate(),'ExamCenter/children2','考试中心',1)
insert into MenuInfo values('考试孙项1',getdate(),'ExamCenter/Nexchildren1','考试中心',2)
insert into MenuInfo values('考试孙项2',getdate(),'ExamCenter/Nexchildren2','考试中心',2)
WITH lmenu(MenuId,MenuName,MenuParentId,level)
as
(
SELECT MenuId,MenuName,MenuParentId,0 level FROM MenuInfo
UNION ALL
SELECT A.MenuId, A.MenuName,b.MenuParentId,b.level+1 FROM MenuInfo A,lmenu b
where a.MenuParentId = b.MenuId
)
select * from lmenu
as
(
SELECT MenuId,MenuName,MenuParentId,0 level FROM MenuInfo
UNION ALL
SELECT A.MenuId, A.MenuName,b.MenuParentId,b.level+1 FROM MenuInfo A,lmenu b
where a.MenuParentId = b.MenuId
)
select * from lmenu
这个递归那里有错？求高手改进下、、、万分感谢
所有回答(2)
0
SELECT MenuId,MenuName,MenuParentId,0 level FROM MenuInfo
UNION ALL 中的“0 level”。是这样的吗？
where MenuParentId=0
我加了一个where条件就可以了
1
WITH lmenu(MenuId,MenuName,MenuParentId,level)
as
(
SELECT MenuId,MenuName,MenuParentId,0 level FROM MenuInfo where MenuParentId=0
UNION ALL
SELECT A.MenuId, A.MenuName,A.MenuParentId,b.level+1 FROM MenuInfo A,lmenu b
where a.MenuParentId = b.MenuId
)
select * from lmenu
as
(
SELECT MenuId,MenuName,MenuParentId,0 level FROM MenuInfo where MenuParentId=0
UNION ALL
SELECT A.MenuId, A.MenuName,A.MenuParentId,b.level+1 FROM MenuInfo A,lmenu b
where a.MenuParentId = b.MenuId
)
select * from lmenu
