请教一条有关分层的SQL语句
表a，有两个字段 id, fid
id fid
1 0
2 1
3 1
4 2
其中查询条件：其中fid是id外键关系，就是说(2,1)的上一层是(1,0)、而(4,2)的上一层是(2,1)，就像是无限分层一样。
如果知道id=4，怎样用一条sql找到最顶的数据，也就是(id,fid)=(1,0)的数据
问题点数：100、回复次数：7Top
1 楼wgsasd311（自强不息）回复于 2006-03-04 19:46:51 得分 20
create table tb(id int,fid int)
insert into tb values(1,0)
insert into tb values(2,1)
insert into tb values(3,1)
insert into tb values(4,2)
go
create proc p(@id int)
as
declare @tb table(id int,fid int,level int)
declare @i int
set @i=1
insert @tb
select id,fid,@i from tb where id=@id
while @@rowcount>0
begin
set @i=@i+1
insert @tb
select a.id,a.fid,@i from tb a,@tb b where a.id=b.fid and b.level=@i-1
end
select top 1 id,fid from @tb order by level desc
go
--测试
exec p 4
exec p 3
drop table tb
drop proc p
goTop
2 楼wgsasd311（自强不息）回复于 2006-03-04 19:54:41 得分 20
create table tb(id int,fid int)
insert into tb values(1,0)
insert into tb values(2,1)
insert into tb values(3,1)
insert into tb values(4,2)
go
create function f_1(@id int)
returns @tb table(id int,fid int,level int)
AS
BEGIN
--declare @tb table(id int,fid int,level int)
declare @i int
set @i=1
insert @tb
select id,fid,@i from tb where id=@id
while @@rowcount>0
begin
set @i=@i+1
insert @tb
select a.id,a.fid,@i from tb a,@tb b where a.id=b.fid and b.level=@i-1
end
return
END
go
--测试
SELECT top 1 id,fid from DBO.F_1(4) order by level desc
drop table tb
drop FUNCTION DBO.F_1
goTop
3 楼dutguoyi（新鲜鱼排）回复于 2006-03-04 20:02:08 得分 20
CREATE FUNCTION f_fid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3))
AS
BEGIN
INSERT @t_Level SELECT @ID
SELECT @ID=fid FROM a
WHERE ID=@ID
AND fid IS NOT NULL
WHILE @@ROWCOUNT>0
BEGIN
INSERT @t_Level SELECT @ID
SELECT @ID=fid FROM a
WHERE ID=@ID
AND fid IS NOT NULL
END
RETURN
END
GOTop
4 楼wgsasd311（自强不息）回复于 2006-03-04 20:13:11 得分 20
create table tb(id int,fid int)
insert into tb values(1,0)
insert into tb values(2,1)
insert into tb values(3,1)
insert into tb values(4,2)
go
create proc p_1(@id int)
AS
declare @sql nvarchar(4000)
set @sql='select @id=fid from tb where id=@id '
exec sp_executesql @sql,N'@id int out',@id out
while @@rowcount>0
begin
set @sql='select @id=fid from tb where id=@id '
exec sp_executesql @sql,N'@id int out',@id out
end
SELECT * from tb where fid=@id
go
--测试
exec p_1 4
drop table tb
drop proc p_1
goTop
5 楼scmail81（琳·风の狼(修罗)）回复于 2006-03-04 20:13:15 得分 20
create table tb(id int,fid int)
insert into tb values(1,0)
insert into tb values(2,1)
insert into tb values(3,1)
insert into tb values(4,2)
create Function Find_id(@id int)
returns @TT table(id int,fid int)
as
begin
declare @T table(id int,fid int,lev int)
declare @lev int
set @lev =1
insert @T select id,fid,@lev from tb where id=@id
while @@rowcount>0
begin
set @lev=@lev+1
insert @T select id,fid,@lev from tb where id in(select fid from @T where lev=@lev-1)
end
insert @TT select T.id,T.fid from @T T where exists(select 1 from (select top 1 WiTh Ties * from @T order by lev DESC) A where T.id=id)
return
end
select * from dbo.Find_id(4)Top
6 楼keenx（老刀）回复于 2006-03-04 20:40:43 得分 0
能不能不用存储过程呢？Top
7 楼wgsasd311（自强不息）回复于 2006-03-05 01:06:00 得分 0
能不能不用存储过程呢？
=======>
用函数也行,我的第二个,楼主看看.Top
