create table tablename(id int ,fid int) go insert into tablename values (1 , 0) insert into tablename values (2, 1) insert into tablename values(3 , 1) insert into tablename values(4, 3) insert into tablename values(5 , 3) insert into tablename values(6, 5) --查询 select fid,id from tablename where fid in ( select B.fid from tablename A inner join tablename B on A.id=B.fid group by B.fid) -----结果 fid id ----------- ----------- 1 2 1 3 3 4 3 5 5 6(所影响的行数为 5 行)
用函数create function fn_Tree(@Id int) returns table @tb (id int ,fid int) as begin insert @tb select id,fid from tablename where fid=@id while exists (select 1 from tablename where fid in (select id from @tb) and id not in (select id from @tb) ) insert @tb select id,fid from tablename where fid in (select id from @tb) and id not in (select id from @tb) return endgo调用: select * from dbo.fn_Tree(0) go select * from dbo.fn_Tree(1 go--没有测试
create table [tb]([id] int identity(1,1),[pid] int,name varchar(20)) insert [tb] select 0,'中国' union all select 0,'美国' union all select 0,'加拿大' union all select 1,'北京' union all select 1,'上海' union all select 1,'江苏' union all select 6,'苏州' union all select 7,'常熟' union all select 6,'南京' union all select 6,'无锡' union all select 2,'纽约' union all select 2,'旧金山' go--查询指定id的所有子 create function f_cid( @id int )returns @re table([id] 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.[id],@l from [tb] a,@re b where a.[pid]=b.[id] and b.[level]=@l-1 end /*--如果只显示最明细的子(下面没有子),则加上这个删除 delete a from @re a where exists( select 1 from [tb] where [pid]=a.[id]) --*/ return end go--调用(查询所有的子) select a.*,层次=b.[level] from [tb] a,f_cid(2)b where a.[id]=b.[id] go
看了楼上的答案,知道自己的错了create function fn_Tree(@Id int) returns @tb table (id int ,fid int) as begin insert @tb select id,fid from tablename where fid=@id while exists (select 1 from tablename where fid in (select id from @tb) and id not in (select id from @tb) ) insert @tb select id,fid from tablename where fid in (select id from @tb) and id not in (select id from @tb) return endgo调用: select * from dbo.fn_Tree(0) go select * from dbo.fn_Tree(1 go--没有测试
某一id下的所有子id都查出来? 那肯定是有where id=''
pengda1i(冒牌大力 V0.3)的代码不显示当前节点。
--建立测试环境 create table A (id char(4),fid char(4))insert into A select '1','0' union all select '2','1' union all select '3','1' union all select '4','3' union all select '5','3' union all select '6','5'select * from Aselect b.id,c.id from A B inner join A C on B.id=C.fid--删除测试环境 drop table A
create table tablename(id int ,fid int)
go
insert into tablename
values (1 , 0)
insert into tablename
values (2, 1)
insert into tablename
values(3 , 1)
insert into tablename
values(4, 3)
insert into tablename
values(5 , 3)
insert into tablename
values(6, 5)
--查询
select fid,id from tablename where fid in (
select B.fid from tablename A inner join tablename B on A.id=B.fid
group by B.fid)
-----结果
fid id
----------- -----------
1 2
1 3
3 4
3 5
5 6(所影响的行数为 5 行)
returns table @tb (id int ,fid int)
as
begin
insert @tb select id,fid from tablename where fid=@id
while exists (select 1 from tablename where fid in (select id from @tb)
and id not in (select id from @tb) )
insert @tb select id,fid from tablename where fid in (select id from @tb)
and id not in (select id from @tb)
return
endgo调用:
select * from dbo.fn_Tree(0)
go
select * from dbo.fn_Tree(1
go--没有测试
insert [tb] select 0,'中国'
union all select 0,'美国'
union all select 0,'加拿大'
union all select 1,'北京'
union all select 1,'上海'
union all select 1,'江苏'
union all select 6,'苏州'
union all select 7,'常熟'
union all select 6,'南京'
union all select 6,'无锡'
union all select 2,'纽约'
union all select 2,'旧金山'
go--查询指定id的所有子
create function f_cid(
@id int
)returns @re table([id] 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.[id],@l
from [tb] a,@re b
where a.[pid]=b.[id] and b.[level]=@l-1
end
/*--如果只显示最明细的子(下面没有子),则加上这个删除
delete a from @re a
where exists(
select 1 from [tb] where [pid]=a.[id])
--*/
return
end
go--调用(查询所有的子)
select a.*,层次=b.[level] from [tb] a,f_cid(2)b where a.[id]=b.[id]
go
returns @tb table (id int ,fid int)
as
begin
insert @tb select id,fid from tablename where fid=@id
while exists (select 1 from tablename where fid in (select id from @tb)
and id not in (select id from @tb) )
insert @tb select id,fid from tablename where fid in (select id from @tb)
and id not in (select id from @tb)
return
endgo调用:
select * from dbo.fn_Tree(0)
go
select * from dbo.fn_Tree(1
go--没有测试
那肯定是有where id=''
create table A (id char(4),fid char(4))insert into A select '1','0'
union all select '2','1'
union all select '3','1'
union all select '4','3'
union all select '5','3'
union all select '6','5'select * from Aselect b.id,c.id from A B inner join A C on B.id=C.fid--删除测试环境
drop table A