22,209
社区成员
发帖
与我相关
我的任务
分享
create table #t(id int,chr varchar(10))
insert into #t
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d' union all
select 5,'e' union all
select 6,'f' union all
select 7,'g' union all
select 8,'h' union all
select 9,'i' union all
select 10,'j'
--最终结果表
create table #result(id int, lid int, chr varchar(10))
--以下进行循环处理
declare @i int,@j int,@jmax int, @k int, @l int, @chr varchar(10)
set @i = 1
while @i < 10
begin
set @j = 1
set @l = 1
if @i = 1
select @jmax = 10
else
select @jmax = max(lid) from #result where id = @i - 1
while @j <= @jmax
begin
set @k = 1
while @k <= 10
begin
set @chr = null
if @i = 1
select @chr = a.chr+b.chr from #t a join #t b on a.chr < b.chr where a.id = @j and b.id = @k
else
select @chr = a.chr+b.chr from #result a join #t b on right(a.chr,1) < b.chr where a.id = @i-1 and a.lid = @j and b.id = @k
if @chr is not null
begin
--你可在此处进行你所谓的复杂处理
insert #result values(@i,@l,@chr)
set @l = @l + 1
end
set @k = @k + 1
end
set @j = @j + 1
end
set @i = @i + 1
end
select * from #result
/*
id lid chr
----------- ----------- ----------
1 1 ab
1 2 ac
1 3 ad
1 4 ae
....
(1013 行受影响)
*/
create table tb(id varchar(10))
insert into tb values('a')
insert into tb values('b')
insert into tb values('c')
insert into tb values('d')
insert into tb values('e')
insert into tb values('f')
insert into tb values('g')
insert into tb values('h')
insert into tb values('i')
insert into tb values('j')
go
select t1.id + t2.id from tb t1 , tb t2 where t1.id < t2.id
union all
select t1.id + t2.id + t3.id from tb t1 , tb t2, tb t3 where t1.id < t2.id and t2.id < t3.id
union all
select t1.id + t2.id + t3.id + t4.id from tb t1 , tb t2, tb t3,tb t4 where t1.id < t2.id and t2.id < t3.id and t3.id < t4.id
union all
select t1.id + t2.id + t3.id + t4.id + t5.id from tb t1 , tb t2, tb t3,tb t4 , tb t5 where t1.id < t2.id and t2.id < t3.id and t3.id < t4.id and t4.id < t5.id
union all
select t1.id + t2.id + t3.id + t4.id + t5.id + t6.id from tb t1 , tb t2, tb t3,tb t4 , tb t5 , tb t6 where t1.id < t2.id and t2.id < t3.id and t3.id < t4.id and t4.id < t5.id and t5.id < t6.id
union all
select t1.id + t2.id + t3.id + t4.id + t5.id + t6.id + t7.id from tb t1 , tb t2, tb t3,tb t4 , tb t5 , tb t6 , tb t7 where t1.id < t2.id and t2.id < t3.id and t3.id < t4.id and t4.id < t5.id and t5.id < t6.id and t6.id < t7.id
union all
select t1.id + t2.id + t3.id + t4.id + t5.id + t6.id + t7.id + t8.id from tb t1 , tb t2, tb t3,tb t4 , tb t5 , tb t6 , tb t7 , tb t8 where t1.id < t2.id and t2.id < t3.id and t3.id < t4.id and t4.id < t5.id and t5.id < t6.id and t6.id < t7.id and t7.id < t8.id
union all
select t1.id + t2.id + t3.id + t4.id + t5.id + t6.id + t7.id + t8.id + t9.id from tb t1 , tb t2, tb t3,tb t4 , tb t5 , tb t6 , tb t7 , tb t8 , tb t9 where t1.id < t2.id and t2.id < t3.id and t3.id < t4.id and t4.id < t5.id and t5.id < t6.id and t6.id < t7.id and t7.id < t8.id and t8.id < t9.id
union all
select t1.id + t2.id + t3.id + t4.id + t5.id + t6.id + t7.id + t8.id + t9.id + t10.id from tb t1 , tb t2, tb t3,tb t4 , tb t5 , tb t6 , tb t7 , tb t8 , tb t9 , tb t10 where t1.id < t2.id and t2.id < t3.id and t3.id < t4.id and t4.id < t5.id and t5.id < t6.id and t6.id < t7.id and t7.id < t8.id and t8.id < t9.id and t9.id < t10.id
drop table tb
/*
------------------------------
ab
ac
ad
ae
af
ag
ah
ai
aj
bc
bd
be
bf
bg
bh
bi
bj
cd
ce
cf
cg
ch
ci
cj
de
df
dg
dh
di
dj
ef
eg
eh
ei
ej
fg
fh
fi
fj
gh
gi
gj
hi
hj
ij
abc
abd
abe
abf
abg
abh
abi
abj
acd
ace
acf
acg
ach
aci
acj
ade
adf
adg
adh
adi
adj
aef
aeg
aeh
aei
aej
afg
afh
afi
afj
agh
agi
agj
ahi
ahj
aij
bcd
bce
bcf
bcg
bch
bci
bcj
bde
bdf
bdg
bdh
bdi
bdj
bef
beg
beh
bei
bej
bfg
bfh
bfi
bfj
bgh
bgi
bgj
bhi
bhj
bij
cde
cdf
cdg
cdh
cdi
cdj
cef
ceg
ceh
cei
cej
cfg
cfh
cfi
cfj
cgh
cgi
cgj
chi
chj
cij
def
deg
deh
dei
dej
dfg
dfh
dfi
dfj
dgh
dgi
dgj
dhi
dhj
dij
efg
efh
efi
efj
egh
egi
egj
ehi
ehj
eij
fgh
fgi
fgj
fhi
fhj
fij
ghi
ghj
gij
hij
...
...
...
abcefghi
abcefghj
abcefgij
abcefhij
abceghij
abcfghij
abdefghi
abdefghj
abdefgij
abdefhij
abdeghij
abdfghij
abefghij
acdefghi
acdefghj
acdefgij
acdefhij
acdeghij
acdfghij
acefghij
adefghij
bcdefghi
bcdefghj
bcdefgij
bcdefhij
bcdeghij
bcdfghij
bcefghij
bdefghij
cdefghij
abcdefghi
abcdefghj
abcdefgij
abcdefhij
abcdeghij
abcdfghij
abcefghij
abdefghij
acdefghij
bcdefghij
abcdefghij
(所影响的行数为 1013 行)
*/
create table tb(id varchar(10))
insert into tb values('a')
insert into tb values('b')
insert into tb values('c')
insert into tb values('d')
insert into tb values('e')
insert into tb values('f')
insert into tb values('g')
insert into tb values('h')
insert into tb values('i')
insert into tb values('j')
go
select t1.id + t2.id from tb t1 , tb t2 where t1.id < t2.id
union all
select t1.id + t2.id + t3.id from tb t1 , tb t2, tb t3 where t1.id < t2.id and t2.id < t3.id
drop table tb
/*
------------------------------
ab
ac
ad
ae
af
ag
ah
ai
aj
bc
bd
be
bf
bg
bh
bi
bj
cd
ce
cf
cg
ch
ci
cj
de
df
dg
dh
di
dj
ef
eg
eh
ei
ej
fg
fh
fi
fj
gh
gi
gj
hi
hj
ij
abc
abd
abe
abf
abg
abh
abi
abj
acd
ace
acf
acg
ach
aci
acj
ade
adf
adg
adh
adi
adj
aef
aeg
aeh
aei
aej
afg
afh
afi
afj
agh
agi
agj
ahi
ahj
aij
bcd
bce
bcf
bcg
bch
bci
bcj
bde
bdf
bdg
bdh
bdi
bdj
bef
beg
beh
bei
bej
bfg
bfh
bfi
bfj
bgh
bgi
bgj
bhi
bhj
bij
cde
cdf
cdg
cdh
cdi
cdj
cef
ceg
ceh
cei
cej
cfg
cfh
cfi
cfj
cgh
cgi
cgj
chi
chj
cij
def
deg
deh
dei
dej
dfg
dfh
dfi
dfj
dgh
dgi
dgj
dhi
dhj
dij
efg
efh
efi
efj
egh
egi
egj
ehi
ehj
eij
fgh
fgi
fgj
fhi
fhj
fij
ghi
ghj
gij
hij
(所影响的行数为 165 行)
*/