求一sql语句,看一看,瞧一瞧啊.

[frezzz]

在MS sql中有这样的表:
DeptId DeptName Description ParentDeptId

ONE oneDEPT Test1 TWO
TWO twoDEPT Test2 THREE
THREE threeDEPT Test3 FOUR
FOUR fourDEPT Test4 FIVE
FIVE fiveDEPT Test5 SIX

求:指定一个DeptId的值,查找出它所有的上级DeptId(即ParentDeptId),现在的情况不允许用存取过程;如:指定DeptId='THREE',那结果应为:FOUR,FIVE,SIX;

[chai21cn]

好像不太可能吧
等待高手

[gc_ding]

参考：
--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go

--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,PID,@i from BOM where PID = @ID

while @@rowcount<>0
begin
set @i = @i + 1

insert into @t
select
a.ID,a.PID,@i
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
return
end
go

--执行查询
select ID from dbo.f_getChild(3)
go

--删除测试数据
drop function f_getChild
drop table BOM

--输出结果
/*
5
6
7
*/

[中国风]

create table ta(DeptId varchar(10), DeptName varchar(20), Description varchar(10),
ParentDeptId varchar(10))
insert ta
select 'ONE', 'oneDEPT' , 'Test1' , 'TWO'
union all select 'TWO', 'twoDEPT' , 'Test2' , 'THREE'
union all select 'THREE', 'threeDEPT', 'Test3' , 'FOUR'
union all select 'FOUR', 'fourDEPT', 'Test4' , 'FIVE'
union all select 'FIVE', 'fiveDEPT', 'Test5' , 'SIX'


create function test_f(@DeptId varchar(10))
returns @ta table (DeptId varchar(10), DeptName varchar(20), Description varchar(10),ParentDeptId varchar(10))
as
begin
while exists (select 1 from ta where DeptId=@DeptId)
begin
select @DeptId=ParentDeptId from ta where DeptId=@DeptId
insert @ta select * from ta where DeptId=@DeptId
end
return
end

select * from dbo.test_f('THREE')
DeptId DeptName Description ParentDeptId
---------- -------------------- ----------- ------------
FOUR fourDEPT Test4 FIVE
FIVE fiveDEPT Test5 SIX

（所影响的行数为 2 行）

[中国风]

如果只需要DeptId:
create table ta(DeptId varchar(10), DeptName varchar(20), Description varchar(10),
ParentDeptId varchar(10))
insert ta
select 'ONE', 'oneDEPT' , 'Test1' , 'TWO'
union all select 'TWO', 'twoDEPT' , 'Test2' , 'THREE'
union all select 'THREE', 'threeDEPT', 'Test3' , 'FOUR'
union all select 'FOUR', 'fourDEPT', 'Test4' , 'FIVE'
union all select 'FIVE', 'fiveDEPT', 'Test5' , 'SIX'


create function test_f(@DeptId varchar(10))
returns @ta table (DeptId varchar(10))
as
begin
while exists (select 1 from ta where DeptId=@DeptId)
begin
insert @ta select ParentDeptId from ta where DeptId=@DeptId
select @DeptId=ParentDeptId from ta where DeptId=@DeptId
end
return
end

select * from dbo.test_f('THREE')

drop function test_f
DeptId
----------
FOUR
FIVE
SIX

（所影响的行数为 3 行）

[子陌红尘]

create table test(DeptId varchar(10),DeptName varchar(10),Description varchar(10),ParentDeptId varchar(10))
insert into test select 'ONE','oneDEPT ','Test1','TWO'
insert into test select 'TWO','twoDEPT ','Test2','THREE'
insert into test select 'THREE','threeDEPT','Test3','FOUR'
insert into test select 'FOUR','fourDEPT ','Test4','FIVE'
insert into test select 'FIVE','fiveDEPT ','Test5','SIX'
go


create function f_getParent(@DeptId varchar(10))
returns varchar(50)
as
begin
declare @ret varchar(50)

while exists(select 1 from test where DeptId=@DeptId)
begin
select
@ret=isnull(@ret,'')+','+ParentDeptId,
@DeptId=ParentDeptId
from
test
where
DeptId=@DeptId

end
set @ret=stuff(@ret,1,1,'')
return @ret
end
go

select dbo.f_getParent('THREE')
go

drop function f_getParent
drop table test
go

[proglovercn]

学习……

[frezzz]

非常感谢各位的解答;
我本来想用一条SQL语句解决,再转换成EQL,但昨天想了一下不大可能,因为这里面存在迭代,我想sql语句应该不可以表示迭代;现在只好求其次了,把这个表里的所有记录拿出来放在List里,再用程序去解决,拿出指定DeptId的所有ParentDeptId(直接和间接);
很高兴有这多人回复,这社会还是好人多啊! :)