邹建帮忙

Question

比如：tb1
   code     othercode
    a           b
    b           c
    c           a
    .....      .....
tb2:  code     othercode
    c           b
    b           g
    f           a
    .....      .....

Answer 1

--存储过程可以这样写,不过我估计效率低于用临时表create proc p_qry
@tablename varchar(1000),
@code char(1)
as
declare @tb varchar(8000),@i int
select @tb='',@i=charindex(',',@tablename+',')
while @i>1
select @tb=@tb+' union all select * from ['+left(@tablename,@i-1)+']'
,@tablename=stuff(@tablename,1,@i,'')
,@i=charindex(',',@tablename+',')
set @tb=stuff(@tb,1,11,'')
print('
select a.code,a.othercode,code1=b.code
from ('+@tb+')a 
join ('+@tb+')b on a.code=b.othercode and a.code='''+@code+'''
where exists(select 1 from ('+@tb+')aaa where code=a.othercode and othercode=b.code)
')
go--调用
exec p_qry 'tb1,tb2,tb5,tb24','a'