34,588
社区成员
发帖
与我相关
我的任务
分享
create index index_tb_a2 on tb(a2)
create index index_tb_a4 on tb(a4)
create table #temp(a2 varchar(50))
create table #temp1(a2 varchar(50))
declare @temp_name varchar(50)
declare @temp_a2 varchar(50)
declare @temp_a4 varchar(50)
declare @flag bit
declare @result bit
declare mycursor cursor for select a2,a4
from [tb]
order by a2 asc
open mycursor
fetch next from mycursor
into @temp_a2,@temp_a4
while(@@fetch_status=0)
begin
set @flag=1
set @result=0
if(not exists(select * from #temp where a2=@temp_a2))
begin
truncate table #temp1
insert into #temp1 values(@temp_a2)
while(@flag=1)
begin
insert into #temp1 values(@temp_a4)
if(exists(select * from [tb] where [a2]=@temp_a4))
begin
select @temp_name=[a4] from [tb] where [a2]=@temp_a4
set @temp_a4=@temp_name
if(@temp_a4=@temp_a2)
begin
set @result=1
set @flag=0
end
end
else
begin
set @result=0
set @flag=0
end
end
if(@result=1)
insert into #temp select * from #temp1
end
fetch next from mycursor
into @temp_a2,@temp_a4
end
close mycursor
deallocate mycursor
select a.*
from [tb] a inner join #temp b on a.a2=b.a2
drop table #temp
drop table #temp1
GO
create table [tb]([a1] int,[a2] varchar(10),[a3] int,[a4] varchar(10))
insert [tb] select 1,'名称1',100,'名称2'
union all select 2,'名称3',10,'名称4'
union all select 3,'名称2',20,'名称5'
union all select 4,'名称6',20,'名称7'
union all select 5,'名称5',300,'名称1'
union all select 6,'名称2',20,'名称1'
go
select a.* from tb a inner join tb b on a.a1<>b.a1 where a.a2=b.a4 and a.a4=b.a2
select a.* from tb a inner join tb b on a.a1<>b.a1 inner join tb c on c.a1<>b.a1
where c.a1<>a.a1 and a.a4=b.a2 and b.a4=c.a2 and c.a4=a.a2 order by a1
go
drop table tb
/*
a1 a2 a3 a4
----------- ---------- ----------- ----------
1 名称1 100 名称2
6 名称2 20 名称1
(2 行受影响)
a1 a2 a3 a4
----------- ---------- ----------- ----------
1 名称1 100 名称2
3 名称2 20 名称5
5 名称5 300 名称1
(3 行受影响)
*/
--sql 2005的一种解法:
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a1] int,[a2] varchar(5),[a3] int,[a4] varchar(5))
insert [tb]
select 1,'名称1',100,'名称2' union all
select 2,'名称3',10,'名称4' union all
select 3,'名称2',20,'名称5' union all
select 4,'名称6',20,'名称7' union all
select 5,'名称5',300,'名称1'
go
--select * from [tb]
declare @lvl int
set @lvl=3 --在此定义循环长度
;with szx as
(
select *,rootid=a1,lvl=1 from tb
union all
select b.*,a.rootid,a.lvl+1
from szx a join tb b on a.a4=b.a2
and a.a1<b.a1 and a.rootid<>b.a1
)
select a1,a2,a3,a4 from szx
where rootid in (select rootid from szx where lvl=@lvl)
/*
a1 a2 a3 a4
----------- ----- ----------- -----
1 名称1 100 名称2
3 名称2 20 名称5
5 名称5 300 名称1
(3 行受影响)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a1] int,[a2] varchar(10),[a3] int,[a4] varchar(10))
insert [tb] select 1,'名称1',100,'名称2'
union all select 2,'名称3',10,'名称4'
union all select 3,'名称2',20,'名称5'
union all select 4,'名称6',20,'名称7'
union all select 5,'名称5',300,'名称1'
union all select 6,'名称10',300,'名称11'
union all select 7,'名称11',300,'名称10'
union all select 8,'名称12',300,'名称13'
union all select 9,'名称14',300,'名称15'
union all select 10,'名称20',300,'名称21'
union all select 11,'名称22',300,'名称24'
union all select 12,'名称21',300,'名称26'
union all select 13,'名称26',300,'名称27'
union all select 14,'名称27',300,'名称28'
union all select 15,'名称28',300,'名称20'
union all select 16,'名称30',300,'名称30'
if object_id('fn_IsCyc') is not null drop function fn_IsCyc
go
create function fn_IsCyc
(
@name1 varchar(10),
@name2 varchar(10)
)
returns int
as
begin
declare @name varchar(10),@result int
set @result=0
if(@name1=@name2) set @result=1
else
begin
if(exists(select * from [tb] where [a2]=@name2))
begin
select @name=[a4] from [tb] where [a2]=@name2
select @result=dbo.fn_IsCyc(@name1,@name)
end
else
set @result=0
end
return @result
end
select * from [tb] where dbo.fn_IsCyc([a2],[a4])=1
if object_id('t_a') is not null drop table t_a
GO
insert into t_a
select '名称1',40,'名称2' union all
select '名称2',50,'名称4' union all
select '名称3',60,'名称4' union all
select '名称4',70,'名称3' union all
select '名称5',80,'名称6' union all
select '名称5',90,'名称1' union all
select '名称7',100,'名称4' union all
select '名称5',20,'名称3' union all
select '名称2',10,'名称1' union all
select '名称6',110,'名称5' union all
select '名称4',110,'名称1'union all
select '名称4',110,'名称2'union all
select '名称3',110,'名称2'
GO
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a1] int,[a2] varchar(10),[a3] int,[a4] varchar(10))
insert [tb] select 1,'名称1',100,'名称2'
union all select 2,'名称3',10,'名称4'
union all select 3,'名称2',20,'名称5'
union all select 4,'名称6',20,'名称7'
union all select 5,'名称5',300,'名称1'
union all select 6,'名称10',300,'名称11'
union all select 7,'名称11',300,'名称10'
union all select 8,'名称12',300,'名称13'
union all select 9,'名称14',300,'名称15'
union all select 10,'名称20',300,'名称21'
union all select 11,'名称22',300,'名称24'
union all select 12,'名称21',300,'名称26'
union all select 13,'名称26',300,'名称27'
union all select 14,'名称27',300,'名称28'
union all select 15,'名称28',300,'名称20'
union all select 16,'名称30',300,'名称30'
if object_id('fn_IsCyc') is not null drop function fn_IsCyc
go
create function fn_IsCyc
(
@name1 varchar(10),
@name2 varchar(10)
)
returns int
as
begin
declare @name varchar(10),@result int
set @result=0
if(@name1=@name2) set @result=1
else
begin
if(exists(select * from [tb] where [a2]=@name2 and [a4]=@name1))
set @result=1
else
begin
if(exists(select * from [tb] where [a2]=@name2))
begin
select @name=[a4] from [tb] where [a2]=@name2
select @result=dbo.fn_IsCyc(@name1,@name)
end
else
set @result=0
end
end
return @result
end
select * from [tb] where dbo.fn_IsCyc([a2],[a4])=1
select tableA.* from A as tableA
inner join A as tableB on ...