有一个表的数据如下
ID Name ParentID
1 菜单1 null
2 菜单2 1
3 菜单3 2
这是一个菜单表,菜单的结构是:
菜单1的子菜单是菜单2
菜单2的子菜单是菜单3
现在我想知道菜单1下总共有几级子菜单(上面的数据应该是2)不知道怎么写查询语句???
那位帮帮忙忙
ID Name ParentID
1 菜单1 null
2 菜单2 1
3 菜单3 2
这是一个菜单表,菜单的结构是:
菜单1的子菜单是菜单2
菜单2的子菜单是菜单3
现在我想知道菜单1下总共有几级子菜单(上面的数据应该是2)不知道怎么写查询语句???
那位帮帮忙忙
create table caidan
(ID int identity(1,1),
FName varchar(20) not null,
ParentID int
)
insert into caidan
select '菜单1',null
union all select '菜单2',1
union all select '菜单3',2with A
as
(select * from caidan where ID=1
union all
select C.* from caidan B,caidan C where B.ID=c.ParentID )select * from A
还没完全把with弄懂,想写一个
用CTE递归最后查询的时候
select LEVEL from f where Name='菜单1'
create table caidan
(ID int identity(1,1),
FName varchar(20) not null,
ParentID int
)
insert into caidan
select '菜单1',null
union all select '菜单2',1
union all select '菜单2',2
union all select '菜单4',null
union all select '菜单5',3
union all select '菜单6',4select * from caidan;with A as
(select * from caidan where Fname='菜单1'
union all
select B.* from caidan as B inner join A on B.ParentID=A.ID) select * from A这下对了,要求共有几级菜单很好求了
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-04-14 19:22:08
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[Name] varchar(5),[ParentID] int)
insert [tb]
select 1,'菜单1',null union all
select 2,'菜单2',1 union all
select 3,'菜单3',2
--------------开始查询--------------------------
;with f as
(
select *,level=0 from tb where Name='菜单1'
union all
select a.*,level+1 from tb a , f b where a.ParentID=b.ID
)
select max(level) from f
----------------结果----------------------------
/* -----------
2(1 行受影响)
*/