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
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
where not exists (
select 1 from tablename where pid=a.cid
)
整个树底下,不用函数
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 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--删除测试
drop table 表
drop function f_id/*--测试结果
cid pid
----------- -----------
1 0
4 1(所影响的行数为 2 行)
--*/
select * from 表 a where exists(select 1 from 表 where cid=a.pid)
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--删除测试
drop table 表/*--测试结果cid pid
----------- -----------
4 1
5 2
6 3(所影响的行数为 3 行)
--*/
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
endtable 结构
cid ,pid,nameselect * from get_cid(1)id name level
----------- -------------------- -----------
1 0
4 食品 1
35 调料 2
36 冻品 2
37 干货 2
38 鲜货 2
39 烟酒饮料小食 2
111 OK汁 3
112 S`S`生油 3
113 八角粉 3
114 白醋 3
怎么结果不对啊?
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