create table a1 ( fid int primary key , pid int, fname varchar(20) ) insert into a1(fid,fname,pid) select 1,'A',0 union select 2,'A1',1 union select 3,'A2',1 union select 4,'A1_1',2 union select 5,'B',0 go create proc pa1_allSon @fid int as declare @int1 tinyint; set @int1=1; select * into #t1 from a1 where pid=@fid; alter table #t1 add flevel tinyint; update #t1 set flevel=1; while @int1<=255 begin insert into #t1(fid,fname,pid,flevel) select a1.fid,a1.fname,a1.pid,@int1+1 from a1 inner join #t1 b on a1.pid=b.fid where b.flevel=@int1 if @@ROWCOUNT=0 break; set @int1=@int1+1; end select * from #t1; drop table #t1; exec pa1_allS其实我觉得用递归不是好思路
呃,和楼上一样,用的是SQL2000时,没有CTE的老办法~~--根据 父类id 获取所有 子类 的函数 CREATE FUNCTION dbo.GETSONS(@pid INT) RETURNS @T TABLE(id INT,name NVARCHAR(50),pid INT,DEEP INT) AS BEGIN DECLARE @CURRENT INT SET @CURRENT=1 INSERT INTO @T SELECT id,name,pid,@CURRENT FROM tb WHERE pid=@pid WHILE @@ROWCOUNT>0 BEGIN SET @CURRENT=@CURRENT+1 INSERT INTO @T SELECT A.id,A.name,A.pid,@CURRENT FROM tb A JOIN @T B ON A.pid=B.id WHERE B.DEEP=@CURRENT-1 END RETURN END --根据 子类id 获取所有 父类 的函数 CREATE FUNCTION dbo.GETPARENTS(@cid INT) RETURNS @T TABLE(id INT,name NVARCHAR(50),pid INT,DEEP INT) AS BEGIN DECLARE @CURRENT INT SET @CURRENT=1 INSERT INTO @T SELECT A.id,A.name,A.pid,@CURRENT FROM tb A LEFT JOIN tb B ON A.id=B.pid WHERE B.id=@cid WHILE @@ROWCOUNT>0 BEGIN SET @CURRENT=@CURRENT+1 INSERT INTO @T SELECT A.id,A.name,A.pid,@CURRENT FROM tb A JOIN @T B ON A.id=B.pid WHERE B.DEEP=@CURRENT-1 END RETURN END
如果是sql server 2005,采用cte比较容易解决。 如果是sql server 2000,处理相对比较麻烦。 可以参考下面链接中的方法: http://bbs.csdn.net/topics/360215000
create table a1
( fid int primary key ,
pid int,
fname varchar(20)
)
insert into a1(fid,fname,pid)
select 1,'A',0 union
select 2,'A1',1 union
select 3,'A2',1 union
select 4,'A1_1',2 union
select 5,'B',0
go
create proc pa1_allSon
@fid int
as
declare @int1 tinyint;
set @int1=1;
select * into #t1 from a1 where pid=@fid;
alter table #t1 add flevel tinyint;
update #t1 set flevel=1;
while @int1<=255
begin
insert into #t1(fid,fname,pid,flevel)
select a1.fid,a1.fname,a1.pid,@int1+1
from a1
inner join #t1 b on a1.pid=b.fid
where b.flevel=@int1
if @@ROWCOUNT=0 break;
set @int1=@int1+1;
end
select * from #t1;
drop table #t1;
exec pa1_allS其实我觉得用递归不是好思路
CREATE FUNCTION dbo.GETSONS(@pid INT)
RETURNS @T TABLE(id INT,name NVARCHAR(50),pid INT,DEEP INT)
AS
BEGIN
DECLARE @CURRENT INT
SET @CURRENT=1
INSERT INTO @T
SELECT id,name,pid,@CURRENT FROM tb WHERE pid=@pid
WHILE @@ROWCOUNT>0
BEGIN
SET @CURRENT=@CURRENT+1
INSERT INTO @T
SELECT A.id,A.name,A.pid,@CURRENT
FROM tb A JOIN @T B ON A.pid=B.id
WHERE B.DEEP=@CURRENT-1
END
RETURN
END
--根据 子类id 获取所有 父类 的函数
CREATE FUNCTION dbo.GETPARENTS(@cid INT)
RETURNS @T TABLE(id INT,name NVARCHAR(50),pid INT,DEEP INT)
AS
BEGIN
DECLARE @CURRENT INT
SET @CURRENT=1
INSERT INTO @T
SELECT A.id,A.name,A.pid,@CURRENT
FROM tb A
LEFT JOIN tb B ON A.id=B.pid
WHERE B.id=@cid
WHILE @@ROWCOUNT>0
BEGIN
SET @CURRENT=@CURRENT+1
INSERT INTO @T
SELECT A.id,A.name,A.pid,@CURRENT
FROM tb A JOIN @T B ON A.id=B.pid
WHERE B.DEEP=@CURRENT-1
END
RETURN
END
如果是sql server 2000,处理相对比较麻烦。
可以参考下面链接中的方法:
http://bbs.csdn.net/topics/360215000