if object_id('t1')is not null
drop table t1
go
create table t1
(
id int identity(1,1),
code int,
parentcode int
)
go
insert t1 select 1,null
union all select 2,1
union all select 3,1
union all select 4,2
union all select 5,3
union all select 6,4
union all select 7,5
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_cid]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_cid]
GO
create function f_cid(
@id int
)returns @re table(code int,[level] int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.code,@l
from [t1] a,@re b
where a.parentcode=b.code and b.[level]=@l-1
end
return
end
go
select * from t1
--调用(查询所有的子)
select a.*,层次=b.[level] from [t1] a,f_cid(2)b where a.code=b.code
drop table t1
go
create table t1
(
id int identity(1,1),
code int,
parentcode int
)
go
insert t1 select 1,null
union all select 2,1
union all select 3,1
union all select 4,2
union all select 5,3
union all select 6,4
union all select 7,5
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_cid]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_cid]
GO
create function f_cid(
@id int
)returns @re table(code int,[level] int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.code,@l
from [t1] a,@re b
where a.parentcode=b.code and b.[level]=@l-1
end
return
end
go
select * from t1
--调用(查询所有的子)
select a.*,层次=b.[level] from [t1] a,f_cid(2)b where a.code=b.code
go
--------------------------------------------
--一个递归问题: go if object_id('dept')is not null drop table dept go create table dept ( deptId int, deptPid int, deptName varchar(20) ) go insert dept select 1, 0, '财务部' union select 2, 0, '市场部' union select 3, 0, 'IT部' union select 4, 3, '开发部' union select 5, 3, '测试部' union select 6, 4, '美工' select * from dept /* 要求结果如下: deptId deptName 1 财务部 2 市场部 3 IT部 4 IT部-开发部 5 IT部-测试部 6 IT部-开发部-美工 */ --典型递归问题 ;with T as ( select deptid,deptpid, cast(deptName as varchar(max))as dname from dept where deptpid=0 union all select d.deptId,T.deptPid,T.dname+'-'+d.deptName from T, dept d where T.deptid=d.deptpid ) select deptid,dname as deptName from T /* deptid deptName 1 财务部 2 市场部 3 IT部 4 IT部-开发部 5 IT部-测试部 6 IT部-开发部-美工 */