create proc proc_mine
@getuserid varchar(6),@getspuserid varchar(6)
as
begin
while(exists(select @getuserid=userID from users where speriorID=@getspuserid))
begin
print @getuserid
@getspuserid=@getuserid
end
end
go
@getuserid varchar(6),@getspuserid varchar(6)
as
begin
while(exists(select @getuserid=userID from users where speriorID=@getspuserid))
begin
print @getuserid
@getspuserid=@getuserid
end
end
go
create proc proc_mine
@getspuserid varchar(6) output
as
begin
declare @getuserid varchar(6)
while(exists(select userID from users where superiorID=@getspuserid))
begin
select @getuserid=userID from users where superiorID=@getspuserid
print @getuserid
set @getspuserid=@getuserid
end
end
go
exec proc_mine '123456'
insert 表 select 1, 'AA' , 0
union all select 2 , 'BB' , 1
union all select 3 , 'CC' , 1
union all select 4 , 'DD' , 2
union all select 5 , 'EE' , 3
union all select 6 , 'FF', 5create function bom (@name char(2))
returns @tb table (levelid int,levelname char(2),parent int)
as
begin
insert @tb select levelID,LevelName,parent from 表 where Levelname = @name
while @@rowcount > 0
insert @tb select levelID,LevelName,parent from 表
where parent in (select levelID from @tb)
and levelID not in (select levelID from @tb)
return
endselect * from dbo.bom('bb')
levelid levelname parent
----------- --------- -----------
2 BB 1
4 DD 2(所影响的行数为 2 行)
--邹建 2003.9--*/
create table 表(userID char(6), superiorID char(6))
insert 表 select '000001','000002'
union all select '000002','000003'
union all select '000003','112355'
union all select '000011','111111'
union all select '000012','111111'
union all select '111111','123456'
union all select '112355','123456'go
create function bom (@id char(6))
returns @tb table (userID char(6),superiorID char(6))
as
begin
insert @tb select userID,superiorID from 表 where superiorID = @id
while @@rowcount > 0
insert @tb select userID,superiorID from 表
where superiorID in (select userID from @tb)
and userID not in (select userID from @tb)
return
end
go
--调用:
select * from dbo.bom('123456')
userID superiorID
------ ----------
111111 123456
112355 123456
000003 112355
000011 111111
000012 111111
000002 000003
000001 000002(所影响的行数为 7 行)
select * from dbo.bom('111111')
userID superiorID
------ ----------
000011 111111
000012 111111(所影响的行数为 2 行)