對ElderBrother()的方法改進:
declare @a table(id1 char(1),r1 int,id2 char(2) ,r2 int )
insert into @a values ('A',1,'B',1)
insert into @a values ('A',1,'C',2)
insert into @a values ('A',2,'D',3)
insert into @a values ('A',2,'E',3)
select a.id2,a.r2,b.id2,b.r2
from @a a join @a b on a.id1=b.id1 and a.id2<>b.id2
union
select a.id1,a.r1,b.id1,b.r1
from @a a join @a b on a.id2=b.id2 and a.id1<>b.id1
union
select a.id2,a.r2,b.id1,b.r1
from @a a join @a b on a.id1=b.id2 and a.id2<>b.id1
union --这个也要加上
select a.id1,a.r1,b.id2,b.r2
from @a a join @a b on a.id2=b.id1 and a.id1<>b.id2
union
select a.id1,a.r1,c.id2,c.r2
from @a a join @a b on a.id2=b.id1 and a.id1<>b.id2 join @a c on b.id2=c.id1 and b.id1<>c.id2
union
select a.id2,a.r2,c.id1,c.r1
from @a a join @a b on a.id1=b.id2 and a.id2<>b.id1 join @a c on b.id1=c.id2 and b.id2<>c.id1
有多层嵌套替代解法:
declare @a table(id1 char(1),id2 char(2))
insert into @a values ('A','B')
insert into @a values ('B','C')
insert into @a values ('C','D')
insert into @a values ('D','E')
insert into @a values ('E','F')
select * into #temp1 from @a
declare @i int
declare @n int
set @i=1
set @n=4 --表示嵌套层数
while (@i<=@n) --
begin
insert into #temp1
select a.id2,b.id2
from #temp1 a join #temp1 b on a.id1=b.id1 and a.id2<>b.id2
union
select a.id1,b.id1
from #temp1 a join #temp1 b on a.id2=b.id2 and a.id1<>b.id1
union
select a.id2,b.id1
from #temp1 a join #temp1 b on a.id1=b.id2 and a.id2<>b.id1
union --这个也要加上
select a.id1,b.id2
from #temp1 a join #temp1 b on a.id2=b.id1 and a.id1<>b.id2
set @i=@i+1
end
select distinct * from #temp1 a
where not exists ( select 1 from @a where id1=a.id1 and id2=a.id2)
drop table #temp1
insert into @a values ('A','B')--B可以替代A
insert into @a values ('A','C')--C可以替代A
insert into @a values ('E','F')--F可以替代E
insert into @a values ('F','G')--G可以替代F
select a.id2,b.id2
from @a a join @a b on a.id1=b.id1 and a.id2<>b.id2
union
select a.id2,b.id2
from @a a join @a b on a.id2=b.id2 and a.id1<>b.id1
union
select a.id2,b.id2
from @a a join @a b on a.id1=b.id2 and a.id2<>b.id1
create function fn_tra(
@c varchar(20)
)
return varchar(20)
as
begin
declare @r varchar(20)
set @r=@c
if exists (select 1 from tablename where 统一代码=@c)
return @c
if exists (select 统一代码 from tablename where 通用代码=@c)
select @r=统一代码 from tablename where 通用代码=@c
return @r
end
go