create table test(dept_no1 int,dept_no2 int)
insert into test values(11 ,101 )
insert into test values(11 ,102 )
insert into test values(11 ,103 )
insert into test values(101 ,10111 )
insert into test values(102 ,10122 )
insert into test values(10111,11111 )
insert into test values(10122,12111 )
insert into test values(11111,111111)
godeclare @t table(dept_no1 int,dept_no2 int)insert into @t(dept_no1,dept_no2)
select t.* from test twhile @@rowcount<>0
begin
insert into @t(dept_no1,dept_no2)
select
a.dept_no1,b.dept_no2
from
test a,@t b
where
a.dept_no2=b.dept_no1
and
not exists(select 1 from @t where dept_no1=a.dept_no1 and dept_no2=b.dept_no2)
end
select * from @t order by dept_no1,dept_no2
go/*
dept_no1 dept_no2
----------- -----------
11 101
11 102
11 103
11 10111
11 10122
11 11111
11 12111
11 111111
101 10111
101 11111
101 111111
102 10122
102 12111
10111 11111
10111 111111
10122 12111
11111 111111
*/drop table test
go
insert into test values(11 ,101 )
insert into test values(11 ,102 )
insert into test values(11 ,103 )
insert into test values(101 ,10111 )
insert into test values(102 ,10122 )
insert into test values(10111,11111 )
insert into test values(10122,12111 )
insert into test values(11111,111111)
godeclare @t table(dept_no1 int,dept_no2 int)insert into @t(dept_no1,dept_no2)
select t.* from test twhile @@rowcount<>0
begin
insert into @t(dept_no1,dept_no2)
select
a.dept_no1,b.dept_no2
from
test a,@t b
where
a.dept_no2=b.dept_no1
and
not exists(select 1 from @t where dept_no1=a.dept_no1 and dept_no2=b.dept_no2)
end
select * from @t order by dept_no1,dept_no2
go/*
dept_no1 dept_no2
----------- -----------
11 101
11 102
11 103
11 10111
11 10122
11 11111
11 12111
11 111111
101 10111
101 11111
101 111111
102 10122
102 12111
10111 11111
10111 111111
10122 12111
11111 111111
*/drop table test
go
insert into test values(11 ,101 )
insert into test values(11 ,102 )
insert into test values(11 ,103 )
insert into test values(101 ,10111 )
insert into test values(102 ,10122 )
insert into test values(10111,11111 )
insert into test values(10122,12111 )
insert into test values(11111,111111)
godeclare @t table(dept_no1 int,dept_no2 int)insert into @t(dept_no1,dept_no2)
select t.* from test twhile @@rowcount<>0
begin
insert into @t(dept_no1,dept_no2)
select
a.dept_no1,b.dept_no2
from
test a,@t b
where
a.dept_no2=b.dept_no1
and
not exists(select 1 from @t where dept_no1=a.dept_no1 and dept_no2=b.dept_no2)
end
select * from @t
union
select dept_no1,dept_no1 from test
union
select dept_no2,dept_no2 from test
order by dept_no1,dept_no2
go/*
dept_no1 dept_no2
----------- -----------
11 11
11 101
11 102
11 103
11 10111
11 10122
11 11111
11 12111
11 111111
101 101
101 10111
101 11111
101 111111
102 102
102 10122
102 12111
103 103
10111 10111
10111 11111
10111 111111
10122 10122
10122 12111
11111 11111
11111 111111
12111 12111
111111 111111
*/drop table test
go
70000A0-01 A028R70000A0
70000A0-02 70000A0-020304
70000A0-020304 A028R70000A0
70000A0-0304 70000A0-020304
70000A0-04 A028R70000A0
对应这样的表就无法得到上面的结果