情况是这样的有一个表,表结构为:
GUID号: planGUID
外键主ID号: mainID
名称: planName
父结点GUID号(PlanGUID): PPlanID想实现通过输入mainID号,将所有记录的mainID=输入的mainID号的记录按层次关系返回记录集(数据表),以下的代码片断返回的数据表记录大量重复,不知道什么问题,请高手指教,或还有别的方式实现。以下为代码片断:
CREATE FUNCTION dbo.F_CCP( @QueryMainPlanID, @parentID )
RETURNS @tbResult TABLE ( @Name ,@ID )
begin
declare @PID varchar(50)
declare MyCursor cursor local for
SELECT PlanGUID FROM T_P_CCP WHERE MainPlanID = @QueryMainPlanID and PPlanID=@parentID
open MyCursor
fetch MyCursor into @PID
while @@FETCH_STATUS=0
begin
insert into @tbResult select * from T_P_CCP where PlanGUID =@PID
declare @t int
set @t = @PlanDepth + 1
insert into @tbResult select * from dbo.F_CCP(@QueryMainPlanID,@PID, @t )
fetch MyCursor into @PID
end
close Mycursor
deallocate MyCursor
RETURN
end
GUID号: planGUID
外键主ID号: mainID
名称: planName
父结点GUID号(PlanGUID): PPlanID想实现通过输入mainID号,将所有记录的mainID=输入的mainID号的记录按层次关系返回记录集(数据表),以下的代码片断返回的数据表记录大量重复,不知道什么问题,请高手指教,或还有别的方式实现。以下为代码片断:
CREATE FUNCTION dbo.F_CCP( @QueryMainPlanID, @parentID )
RETURNS @tbResult TABLE ( @Name ,@ID )
begin
declare @PID varchar(50)
declare MyCursor cursor local for
SELECT PlanGUID FROM T_P_CCP WHERE MainPlanID = @QueryMainPlanID and PPlanID=@parentID
open MyCursor
fetch MyCursor into @PID
while @@FETCH_STATUS=0
begin
insert into @tbResult select * from T_P_CCP where PlanGUID =@PID
declare @t int
set @t = @PlanDepth + 1
insert into @tbResult select * from dbo.F_CCP(@QueryMainPlanID,@PID, @t )
fetch MyCursor into @PID
end
close Mycursor
deallocate MyCursor
RETURN
end
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
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--输出结果
/*
ID
----
5
6
7
*/--删除测试数据
drop function f_getChild
drop table BOM
-----------------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION F_CCP(@MainID int, @PID int)
returns @tbResult table (ID int,Name varchar(20))
begin
declare @PID varchar(50) declare MyCursor cursor for
SELECT PlanGUID FROM T_P_CCP WHERE MainPlanID= @MainID and PPlanID=@PID
open MyCursor fetch next from MyCursor into @PID while @@FETCH_STATUS=0
begin insert into @tbResult(ID,Name)
select planGUID,planName from T_P_CCP where PlanGUID=@PID while @@rowcount<>0
begin insert into @tbResult(ID,Name)
select
a.planGUID,a.planName
from
T_P_CCP a,@tbResult b
where
a.PPlanID=b.ID
and
not exists(select 1 from @tbResult where ID=a.PPlanID) end fetch next from MyCursor into @PID end
close Mycursor
deallocate MyCursor
return
end
go
insert into @tbResult select * from dbo.F_CCP(@QueryMainPlanID,@PID, @t )参数个数都不同,
@phone_main varchar(20)
as
create table # (child_phone varchar(20))
declare @is_phone_main varchar(20)
declare @is_phone_sub varchar(20) declare cursor1 cursor for select phone_main,phone_sub from bom where phone_main = @phone_main
open cursor1 fetch next from cursor1 into @is_phone_main , @is_phone_sub
--select @is_phone_main = @is_phone_sub while @@fetch_status = 0
begin
insert into # select @is_phone_sub
select @is_phone_sub = phone_sub from bom where phone_main = @is_phone_sub
while (@@rowcount <> 0)
begin
insert into #
select @is_phone_sub
select @is_phone_sub = phone_sub from bom where phone_main = @is_phone_sub
end
fetch next from cursor1 into @is_phone_main , @is_phone_sub
end close cursor1
deallocate cursor1select * from #