自关联问题求助
artr000001   1  
artr000002   artr000001  
artr000003   artr000002  
artr000004   artr000003

我要找出第二列等于第一列的全部数据   比如给出参数   artr000001   则找出   artr000001，artr000002，artr000003，artr000004   给出参数artr000003   则找出   artr000003，artr000004     有没有办法用一个sql实现

------解决方案--------------------
--生成测试数据
create table BOM(col1 VARCHAR(1000),col2 VARCHAR(1000))
insert into BOM select 'artr000001 ', '1 '
insert into BOM select 'artr000002 ', 'artr000001 '
insert into BOM select 'artr000003 ', 'artr000002 '
insert into BOM select 'artr000004 ', 'artr000003 '
go

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

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

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

--执行查询
select col1 from dbo.f_getChild( 'artr000001 ')
go

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

--输出结果
/*
col1
----
artr000002
artr000003
artr000004
*/
------解决方案--------------------
--这样也许简单一点
create table #a(a varchar(20),b varchar(20))
insert #a
select 'artr000001 ', '1 ' union all
select 'artr000002 ', 'artr000001 ' union all
select 'artr000003 ', 'artr000002 ' union all
select 'artr000004 ', 'artr000003 '

declare @a varchar(20),@b varchar(20)

set @a = 'artr000001 '

create table #b(a varchar(20))

insert #b select @a

select @a = a from #a where b = @a

while @@ROWCOUNT > 0
begin
insert #b
select @a
select @a = a from #a where b = @a
end

select * from #b

drop table #a,#b


------解决方案--------------------

create table tt
(s1 char(10),s2 char(10))
Go

insert into tt
select 'artr000001 ', '1 ' UNION ALL
select 'artr000002 ', 'artr000001 ' UNION ALL
select 'artr000003 ', 'artr000002 ' UNION ALL
select 'artr000004 ', 'artr000003 '

CREATE FUNCTION dbo.FUNC_GETSUBYYBSTR(@i_str AS VARCHAR(10))
RETURNS VARCHAR(4000)
AS
BEGIN
declare @return_str VARCHAR(4000)
select @return_str=@i_str;

WITH tb_Subtree(s1)
AS
(
SELECT s1
FROM tt
WHERE s2=@i_str

UNION ALL

SELECT e.s1
FROM tt AS e
INNER JOIN tb_Subtree AS es
ON e.s2 = es.s1
)

SELECT @return_str=@return_str+ ', '+s1 FROM (select DISTINCT s1 FROM tb_Subtree) m
if @return_str=@i_str
select @return_str= ' '

RETURN @return_str
END
go

select dbo.FU