--这个可以吧 if exists(select* from sysobjects where xtype='U' and name='test1') drop table test1 go create table test1( id int, nu char) go if exists(select* from sysobjects where xtype='U' and name='test2') drop table test2 go create table test2( id int, nu char) go insert test1 select 1111, 'A' union all select 2222, 'B' union all select 3333, 'C' union all select 4444, 'E' union all select 5555, 'F' union all select 6666, 'D' union all select 7777, 'D'insert test2 select 1111, 'B' union all select 2222, 'C' union all select 3333, 'D' union all select 4444, 'F' union all select 5555, 'H' union all select 6666, 'I' union all select 7777, 'G'--主要部分 if exists(select* from sysobjects where xtype='P' and name='getequals') drop proc getequals go create proc getequals ( @ch char) as begin declare @ch1 char declare @ch2 char declare @table table (ch char) insert @table select @ch declare cur cursor for select test1.nu, test2.nu from test1, test2 where test1.id=test2.idopen cur fetch next from cur into @ch1, @ch2 while @@fetch_status=0 begin if exists(select * from @table where ch=@ch1 ) insert into @table select @ch2 else if exists(select * from @table where ch=@ch2 ) insert into @table select @ch1fetch next from cur into @ch1, @ch2 end close cur deallocate cur select * from @table where ch<>@ch end go --测试示例 exec getequals 'B'
To: xiaoxiao523 () 我在另外一贴里面给了,我要分哦
---------------建立测试环境---------------------- CREATE TABLE [test1] ( [id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL , [NU] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GOCREATE TABLE [test2] ( [id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL , [NU] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GOinsert test1(id,NU) values('1111','A') insert test1(id,NU) values('2222','B') insert test1(id,NU) values('3333','C') insert test1(id,NU) values('4444','E') insert test1(id,NU) values('5555','F') insert test1(id,NU) values('6666','D') insert test1(id,NU) values('7777','D')insert test2(id,NU) values('1111','B') insert test2(id,NU) values('2222','C') insert test2(id,NU) values('3333','D') insert test2(id,NU) values('4444','F') insert test2(id,NU) values('5555','H') insert test2(id,NU) values('6666','I') insert test2(id,NU) values('7777','G') ---------------建立测试环境---------------------- -------------建一个函数----------------------- SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GOCREATE FUNCTION dbo.getvalue (@k nvarchar(10)) RETURNS nvarchar(4000) AS BEGIN declare @Text nvarchar(4000)declare @id nvarchar(10) declare @Nu1 nvarchar(10) declare @Nu2 nvarchar(10)set @Text='' DECLARE Employee_Cursor CURSOR FOR select a.id,a.NU,b.NU FROM test1 a,test2 b where a.id=b.id and a.nu =@k OPEN Employee_Cursor fetch next from Employee_Cursor into @id,@Nu1,@Nu2 while @@FETCH_STATUS=0 begin begin set @Text= @Text+@Nu2+dbo.getvalue(@Nu2) end fetch next from Employee_Cursor into @id,@Nu1,@Nu2 end return @Text close Employee_Cousor deallocate Employee_Cousor --set @text='kkk' return @Text END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO -------------建一个函数--------------------------测试 select dbo.getvalue('A') select dbo.getvalue('B') select dbo.getvalue('E')---测试 -- drop table test1 drop table test2
不用那么复杂的,这个简单的函数就可以了。Create Function GetNU(@NU Varchar(100)) Returns Varchar(1000) AS Begin Declare @S Varchar(1000) Declare @T Table(NU Varchar(10)) Set @S='' Insert @T Select NU from test2 Where ID IN (Select ID from test1 Where NU=@NU) While @@ROWCOUNT>0 Insert @T Select NU from test2 Where ID IN (Select ID from test1 where NU IN (Select NU from @T)) And NU Not IN (Select * from @T) Select @S=@S+NU from (Select Distinct NU from @T)A Return @S End
--建立测试环境 Create table test1 (ID Int, NU Varchar(10))Create table test2 (ID Int, NU Varchar(10)) --插入数据 Insert test1 Values(1111, 'A') Insert test1 Values(2222, 'B') Insert test1 Values(3333, 'C') Insert test1 Values(4444, 'E') Insert test1 Values(5555, 'F') Insert test1 Values(6666, 'D') Insert test1 Values(7777, 'D')Insert test2 Values(1111, 'B') Insert test2 Values(2222, 'C') Insert test2 Values(3333, 'D') Insert test2 Values(4444, 'F') Insert test2 Values(5555, 'H') Insert test2 Values(6666, 'I') Insert test2 Values(7777, 'G') GO --建立函数 Create Function GetNU(@NU Varchar(100)) Returns Varchar(1000) AS Begin Declare @S Varchar(1000) Declare @T Table(NU Varchar(10)) Set @S='' Insert @T Select NU from test2 Where ID IN (Select ID from test1 Where NU=@NU) While @@ROWCOUNT>0 Insert @T Select NU from test2 Where ID IN (Select ID from test1 where NU IN (Select NU from @T)) And NU Not IN (Select * from @T) Select @S=@S+NU from (Select Distinct NU from @T)A Return @S End GO --测试 Select dbo.GetNU('A') Select dbo.GetNU('B') Select dbo.GetNU('E') --删除测试环境 Drop Table test1,test2 Drop Function GetNU --结果 /* BCDGICDGIFH */
典型的樹型分析 你參照一下鄒健寫的 处理示例--示例数据 create table T1(Pid int,subPid int,Quantity int) insert T1 select 102,104,2 union all select 102,105,2 union all select 104,106,3 union all select 104,109,1 union all select 105,107,3 union all select 107,108,1 go--查询处理函数 create function f_cid(@subPid int) returns @re table(subPid int,Quantity int,level int) as begin declare @l int set @l=0 insert @re select subPid,Quantity,@l from T1 where Pid=@subPid while @@rowcount>0 begin set @l=@l+1 insert @re select a.subPid,a.Quantity*b.Quantity,@l from T1 a,@re b where a.Pid=b.subPid and b.level=@l-1 end delete a from @re a where exists( select * from T1 where Pid=a.subPid) return end go--调用实现查询 select subPid,Quantity from f_cid(102) go--删除测试 drop table T1 drop function f_cid/*--结果subPid Quantity ----------- ----------- 106 6 109 2 108 6(所影响的行数为 3 行) --*/
if exists(select* from sysobjects where xtype='U' and name='test1')
drop table test1
go
create table test1( id int, nu char)
go
if exists(select* from sysobjects where xtype='U' and name='test2')
drop table test2
go
create table test2( id int, nu char)
go
insert test1 select 1111, 'A'
union all select 2222, 'B'
union all select 3333, 'C'
union all select 4444, 'E'
union all select 5555, 'F'
union all select 6666, 'D'
union all select 7777, 'D'insert test2 select 1111, 'B'
union all select 2222, 'C'
union all select 3333, 'D'
union all select 4444, 'F'
union all select 5555, 'H'
union all select 6666, 'I'
union all select 7777, 'G'--主要部分
if exists(select* from sysobjects where xtype='P' and name='getequals')
drop proc getequals
go
create proc getequals ( @ch char)
as
begin
declare @ch1 char
declare @ch2 char
declare @table table (ch char)
insert @table select @ch
declare cur cursor for
select test1.nu, test2.nu
from test1, test2
where test1.id=test2.idopen cur
fetch next from cur into @ch1, @ch2
while @@fetch_status=0
begin
if exists(select * from @table where ch=@ch1 )
insert into @table select @ch2
else
if exists(select * from @table where ch=@ch2 )
insert into @table select @ch1fetch next from cur into @ch1, @ch2
end
close cur
deallocate cur
select * from @table where ch<>@ch
end
go
--测试示例
exec getequals 'B'
我在另外一贴里面给了,我要分哦
CREATE TABLE [test1] (
[id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[NU] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [test2] (
[id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[NU] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOinsert test1(id,NU)
values('1111','A')
insert test1(id,NU)
values('2222','B')
insert test1(id,NU)
values('3333','C')
insert test1(id,NU)
values('4444','E')
insert test1(id,NU)
values('5555','F')
insert test1(id,NU)
values('6666','D')
insert test1(id,NU)
values('7777','D')insert test2(id,NU)
values('1111','B')
insert test2(id,NU)
values('2222','C')
insert test2(id,NU)
values('3333','D')
insert test2(id,NU)
values('4444','F')
insert test2(id,NU)
values('5555','H')
insert test2(id,NU)
values('6666','I')
insert test2(id,NU)
values('7777','G')
---------------建立测试环境----------------------
-------------建一个函数-----------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE FUNCTION dbo.getvalue
(@k nvarchar(10))
RETURNS nvarchar(4000) AS
BEGIN
declare @Text nvarchar(4000)declare @id nvarchar(10)
declare @Nu1 nvarchar(10)
declare @Nu2 nvarchar(10)set @Text=''
DECLARE Employee_Cursor CURSOR FOR
select a.id,a.NU,b.NU FROM test1 a,test2 b where a.id=b.id and a.nu =@k
OPEN Employee_Cursor
fetch next from Employee_Cursor into @id,@Nu1,@Nu2
while @@FETCH_STATUS=0
begin
begin
set @Text= @Text+@Nu2+dbo.getvalue(@Nu2)
end
fetch next from Employee_Cursor into @id,@Nu1,@Nu2
end
return @Text
close Employee_Cousor
deallocate Employee_Cousor
--set @text='kkk'
return @Text
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-------------建一个函数--------------------------测试
select dbo.getvalue('A')
select dbo.getvalue('B')
select dbo.getvalue('E')---测试
--
drop table test1
drop table test2
CDIG
FH
楼主,经过测试可以完成你的要求
有一个小的缺陷
select dbo.getvalue('B') 时,由于是有游标已经循环过了"A"所以结果就是"CDIG"
Returns Varchar(1000)
AS
Begin Declare @S Varchar(1000)
Declare @T Table(NU Varchar(10))
Set @S='' Insert @T Select NU from test2 Where ID IN (Select ID from test1 Where NU=@NU)
While @@ROWCOUNT>0
Insert @T Select NU from test2 Where ID IN (Select ID from test1 where NU IN (Select NU from @T)) And NU Not IN (Select * from @T)
Select @S=@S+NU from (Select Distinct NU from @T)A
Return @S
End
Create table test1
(ID Int,
NU Varchar(10))Create table test2
(ID Int,
NU Varchar(10))
--插入数据
Insert test1 Values(1111, 'A')
Insert test1 Values(2222, 'B')
Insert test1 Values(3333, 'C')
Insert test1 Values(4444, 'E')
Insert test1 Values(5555, 'F')
Insert test1 Values(6666, 'D')
Insert test1 Values(7777, 'D')Insert test2 Values(1111, 'B')
Insert test2 Values(2222, 'C')
Insert test2 Values(3333, 'D')
Insert test2 Values(4444, 'F')
Insert test2 Values(5555, 'H')
Insert test2 Values(6666, 'I')
Insert test2 Values(7777, 'G')
GO
--建立函数
Create Function GetNU(@NU Varchar(100))
Returns Varchar(1000)
AS
Begin Declare @S Varchar(1000)
Declare @T Table(NU Varchar(10))
Set @S='' Insert @T Select NU from test2 Where ID IN (Select ID from test1 Where NU=@NU)
While @@ROWCOUNT>0
Insert @T Select NU from test2 Where ID IN (Select ID from test1 where NU IN (Select NU from @T)) And NU Not IN (Select * from @T)
Select @S=@S+NU from (Select Distinct NU from @T)A
Return @S
End
GO
--测试
Select dbo.GetNU('A')
Select dbo.GetNU('B')
Select dbo.GetNU('E')
--删除测试环境
Drop Table test1,test2
Drop Function GetNU
--结果
/*
BCDGICDGIFH
*/
你參照一下鄒健寫的
处理示例--示例数据
create table T1(Pid int,subPid int,Quantity int)
insert T1 select 102,104,2
union all select 102,105,2
union all select 104,106,3
union all select 104,109,1
union all select 105,107,3
union all select 107,108,1
go--查询处理函数
create function f_cid(@subPid int)
returns @re table(subPid int,Quantity int,level int)
as
begin
declare @l int
set @l=0
insert @re select subPid,Quantity,@l
from T1
where Pid=@subPid
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.subPid,a.Quantity*b.Quantity,@l
from T1 a,@re b
where a.Pid=b.subPid and b.level=@l-1
end
delete a from @re a
where exists(
select * from T1 where Pid=a.subPid)
return
end
go--调用实现查询
select subPid,Quantity from f_cid(102)
go--删除测试
drop table T1
drop function f_cid/*--结果subPid Quantity
----------- -----------
106 6
109 2
108 6(所影响的行数为 3 行)
--*/