CREATE PROC AAA @Type_code varchar(15) AS declare @Type_codeTmp varchar(15),@count int create table #aa(id int identity(1,1),Type_code varchar(15)) insert #aa(Type_code) select @Type_code set @count=1 while (select count(1) from #aa)>=@count begin select @Type_codeTmp=Type_code from #aa where id=@count insert #aa(Type_code) select Type_code from tb_Type where parent_type=@Type_codeTmp set @count=@count+1 end select * from #aa ------------ test: create table tb_Type(Type_code varchar(15),parent_type varchar(15)) insert tb_Type(Type_code,parent_type) select 1,0 union all select 2,1 union all select 3,1 union all select 4,1 union all select 5,2 union all select 6,2 union all select 8,0exec aaa 1drop table tb_Type drop proc aaa
你这好象是单编号的递归嘛!如果要查询只需要select * from 表 where tb_type like 'c%'这样就可以了嘛
http://www.dev-club.com/club/bbs/showEssence.asp?id=26765
@Type_code varchar(15)
AS
declare @Type_codeTmp varchar(15),@count int
create table #aa(id int identity(1,1),Type_code varchar(15))
insert #aa(Type_code)
select @Type_code
set @count=1
while (select count(1) from #aa)>=@count
begin
select @Type_codeTmp=Type_code from #aa where id=@count
insert #aa(Type_code)
select Type_code from tb_Type where parent_type=@Type_codeTmp
set @count=@count+1
end
select * from #aa
------------
test:
create table tb_Type(Type_code varchar(15),parent_type varchar(15))
insert tb_Type(Type_code,parent_type)
select 1,0
union all select 2,1
union all select 3,1
union all select 4,1
union all select 5,2
union all select 6,2
union all select 8,0exec aaa 1drop table tb_Type
drop proc aaa