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 [email protected]
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 @[email protected]+ ', '+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 [email protected]
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 @[email protected]+1
INSERT @t_Level SELECT a.PID,@Level
FROM par a,@t_Level b
WHERE a.ID=b.ID
AND [email protected]
END
SELECT @[email protected]+ ', '+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