不用游标,查树所有父id
create   table   par   (id   int,bid   int,pid   int)
insert   into   par   select   1,1,0
union   all   select   2,2,1
union   all   select   3,3,2
union   all   select   4,4,3
union   all   select   5,5,4

/*
效果
id           bid         所有父id
1 1 0
2 2 0,1
3 3 0,1,2
4 4 0,1,2,3
5 5 0,1,2,3,4

别用游标...   不知能实现不？
*/

bid   是本部门id
pid   是父部门(上级)部门id

请各位大大帮帮忙啊

------解决方案--------------------
面试题吧
------解决方案--------------------
create function ff_get(@bid int)
returns varchar(1000)
as
begin
declare @tt varchar(1000)
declare @temp table(id int,bid int,pid int)
insert @temp select * from par where bid=@bid
While @@Rowcount > 0
begin
insert @temp select a.*
from par a inner join @temp b
on a.bid=b.pid
where a.bid not in (select bid from @temp)
end
select @tt= ' '
select @tt=@tt+ ', '+ltrim(pid)
from @temp order by pid
select @tt=right(@tt,len(@tt)-1)
return(@tt)
end


/*调用*/
select id,bid,pid=dbo.ff_get(bid) from par


/*借用鱼老大函数的思路 ^ ^ 呵呵*/


1 1 0
2 2 0,1
3 3 0,1,2
4 4 0,1,2,3
5 5 0,1,2,3,4

------解决方案--------------------
create table par (id int,bid int,pid int)
insert into par select 1,1,0
union all select 2,2,1
union all select 3,3,2
union all select 4,4,3
union all select 5,5,4

go
create function fnTest(@bid int)
returns nvarchar(1000)
as
begin
declare @pid int
declare @result nvarchar(1000)
select @pid=pid from par where bid=@bid
if @pid = 0
set @result = cast(@pid as varchar)
else
set @result = dbo.fnTest(@pid) + ', ' + cast(@pid as varchar);
return @result
end

go
select id,bid,dbo.fnTest(bid) from par

drop table par
drop function fnTest
------解决方案--------------------
create table par (id int,bid int,pid int)
insert into par select 1,1,0
union all select 2,2,1
union all select 3,3,2
union all select 4,4,3
union all select 5,5,4

GO

CREATE FUNCTION f_Pid(@ID varchar(3))
RETURNS VarChar(1000)
AS
BEGIN
DECLARE @t_Level TABLE(ID varchar(3),Level int)
DECLARE @Level int
DECLARE @str varchar(1000)
SET @str= ' '
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT> 0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.PID,@Level
FROM par a,@t_Level b
WHERE a.ID=b.ID
AND b.Level=@Level-1
END
SELECT @str=@str+ ', '+RTRIM(ID) FROM @t_Level WHERE Level> 1 ORDER BY ID
SET @str=STUFF(@str,1,1, ' ')
RETURN @str
END
GO

select ID,bid,DBO.F_Pid(id) from par


DROP FUNCTION f_Pid
drop table par