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
--建立測試環境 Create Table A (id Int, parentid Int, Cname Varchar(10)) Insert A Select 1, null, 'A' Union All Select 2, 1, 'A2' Union All Select 3, 1, 'A3' Union All Select 4, 2, 'A4' Union All Select 5, 4, 'A5'Create Table B (pid Int, pname Varchar(10), typeid Int) Insert B Select 1, 'P1', 1 Union All Select 3, 'P2', 2 Union All Select 5, 'P3', 3 Union All Select 6, 'P4', 4 Union All Select 7, 'P5', 5 GO --建立函數 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 --刪除測試環境 Drop Table A, B Drop Function F_GetChildren --結果 /* pid pname typeid 3 P2 2 6 P4 4 7 P5 5 */
再问一下 我只要获取类表A 某一接点下所有子接点ID 请问怎么写
declare @id int set @id = 1 /*指定分类号*/ select * from 分类表 as a INNER JOIN dbo.fnGetChildren(@id) as b on a.typeid = b.id
wcfboy1(王风) ( ) 信誉:100 Blog 加为好友 2007-07-02 15:55:08 得分: 0
再问一下 我只要获取类表A 某一接点下所有子接点ID 请问怎么写
--------------------------- Select id From dbo.F_GetChildren(2)
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
Create Table A
(id Int,
parentid Int,
Cname Varchar(10))
Insert A Select 1, null, 'A'
Union All Select 2, 1, 'A2'
Union All Select 3, 1, 'A3'
Union All Select 4, 2, 'A4'
Union All Select 5, 4, 'A5'Create Table B
(pid Int,
pname Varchar(10),
typeid Int)
Insert B Select 1, 'P1', 1
Union All Select 3, 'P2', 2
Union All Select 5, 'P3', 3
Union All Select 6, 'P4', 4
Union All Select 7, 'P5', 5
GO
--建立函數
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
--刪除測試環境
Drop Table A, B
Drop Function F_GetChildren
--結果
/*
pid pname typeid
3 P2 2
6 P4 4
7 P5 5
*/
set @id = 1 /*指定分类号*/
select * from 分类表 as a INNER JOIN dbo.fnGetChildren(@id) as b
on a.typeid = b.id
再问一下 我只要获取类表A 某一接点下所有子接点ID 请问怎么写
---------------------------
Select id From dbo.F_GetChildren(2)