--测试数据
create table 表(cid int,pid int)
insert 表 select 1,0
union all select 2,0
union all select 3,0
union all select 4,1
union all select 5,2
union all select 6,3
go
--查询
select * from 表 a where exists(select 1 from 表 where cid=a.pid)
go
--测试数据
create table 表(cid int,pid int)
insert 表 select 1,0
union all select 2,0
union all select 3,0
union all select 4,1
union all select 5,2
union all select 6,3
go
--创建查询函数
create function f_id(@id int)
returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert into @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert into @re select a.cid,@l
from 表 a join @re b on a.pid=b.id and b.level=@l-1
end
return
end
go
--调用实现查询
select a.*
from 表 a join f_id(1) b on a.cid=b.id
go
--创建查询函数
create function f_id(@id int)
returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert into @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert into @re select a.cid,@l
from 表 a join @re b on a.pid=b.id and b.level=@l-1
end
return
end
go
--调用实现查询
select a.*
from 表 a join f_id(1) b on a.cid=b.id
create function f_id(@id int)
returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert into @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert into @re select a.cid,@l
from 表 a join @re b on a.pid=b.id and b.level=@l-1
end
return
end
go
--调用实现查询
select a.*
from 表 join f_id(1) b on a.cid=b.id
改为:
create function Get_Cid(@pid int)
returns @RT Table(id int ,name varchar(20),level int)
as
begin
declare @l int
set @l=0
insert into @Rt select cid,name,@l
from table
where cid=@pid --如果不要第一条记录,则改为: where pid=@pid
while @@rowcount>0
begin
set @l=@l+1
insert into @rt select a.cid,a.name,@L
FROM table A JOIN @RT b ON A.pid=B.id and b.level=@l-1
end
return
end
create function Get_Cid(@pid int)
returns @RT Table(id int ,name varchar(20),level int)
as
begin
declare @l int
declare @name varchar(20)
set @l=0
set @name=''
insert into @Rt select @pid,@name,@l
while @@rowcount>0
begin
set @l=@l+1
insert into @rt select a.cid,a.name,@L
FROM table A JOIN @RT b ON A.pid=B.id and b.level=@l-1
end
return
end