求一比较难的SQL语句（无限级分类问题）
无限级分类表 A
id,parentid ,Cname
图片表B
pid,pname,typeid(类别号关联表A.id)
我想取表A中某一节点下所有的图片，请问怎么写
[解决办法]
if object_id( 'fnGetChildren ') is not null
drop function fnGetChildren
GO
create function fnGetChildren(@ID int)
returns @t Table (ID int)
as
begin
insert @t select ID from 分类表 where ID = @ID
While @@Rowcount > 0
insert @t select a.ID from 分类表 as a inner join @t as b
on a.parentid = b.ID and a.ID not in (select ID from @t)
return
end
GO
----查询
declare @id int
set @id = 1 /*指定分类号*/
select * from 分类表 as a INNER JOIN dbo.fnGetChildren(@id) as b
on a.typeid = b.id
[解决办法]
--建立函數
Create Function F_GetChildren(@id Int)
Returns @Tree Table (id Int, parentid Int)
As
Begin
Insert @Tree Select id, parentid From A Where id = @id
While @@Rowcount > 0
Insert @Tree Select A.id, A.parentid From A A Inner Join @Tree B On A.parentid = B.id And A.id Not In (Select id From @Tree)
Return
End
GO
--測試
Select B.* From dbo.F_GetChildren(2) A Inner Join B On A.id = B.typeid
GO
