自关联问题求助

[myz457]

artr000001 1
artr000002 artr000001
artr000003 artr000002
artr000004 artr000003

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

[myz457]

高手很多啊 可是有没有一个select 实现的呢 不要用函数 存储过程

[myz457]

大概需要嵌套递归什么的 求教高手啊 分可以在加


SELECT a.*
FROM BsContract a, BsContract b
WHERE b.FContractNo = a.FOriContractNo and ( b.FContractNo in ( 'artr000001' ) )

我这样写之可以得到一条artr000002
artr000002对应的artr000003怎么办呢?

[gc_ding]

--生成测试数据
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,@ret varchar(8000)
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

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

--输出结果
/*
5
6
7
*/

[gc_ding]

--生成测试数据
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
*/

[caixia615]

也可以借助临时表加个自增长列通过自关联查询.

[myz457]

楼上两位都正确 但是能不能用一个select出来结果呢

[myz457]

用途就是放在pb的数据窗口 传进参数 得出结果 当然可以使用存储过程作数据源 但是麻烦些 要是一个查询搞定最好了

[mengmou]

--这样也许简单一点
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

[mengmou]

--结果
a
--------------------
artr000001
artr000002
artr000003
artr000004

（所影响的行数为 4 行）

[xiequanqin]

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.FUNC_GETSUBYYBSTR('artr000001')

select dbo.FUNC_GETSUBYYBSTR('artr000003')

[tmc1703]

学习了

[xiequanqin]

artr000001,artr000002,artr000003,artr000004

artr000003,artr000004

[wst0350]

thank

[gc_ding]

高手很多啊 可是有没有一个select 实现的呢 不要用函数 存储过程
-------------------------------------------------------------
就算给楼主找到方法可以一句搞定，语句也必定很长，效率不高

[jxf654]

up