做了个分析写了段程序,结果还不是很理想
create table t_a(old varchar(1),able bit)
insert t_a
select 'A',1 union all
select 'B',1 union all
select 'C',1 union all
select 'a',1 union all
select 'b',0 union all
select 'c',0select id=identity(int,1,1), col1=a.old,col2=b.old
into #t
from t_a a,t_a b
where a.able|b.able=1
and ascii(a.old)<>ascii(b.old)
and ((ascii(a.old)<ascii('Z') and ascii(b.old)<ascii('Z'))
or (ascii(upper(a.old))=ascii(upper(b.old)))
or (ascii(a.old)>=ascii('a') and ascii(b.old)>=ascii('a')))select a.col1,a.col2,b.col1,b.col2,c.col1,c.col2
from #t a,#t b,#t c
where a.id<>b.id and a.id<>c.id and b.id<>c.id
and ascii(a.col1)<>ascii(b.col1) and ascii(a.col1)<>ascii(b.col2) and ascii(a.col1)<>ascii(c.col1) and ascii(a.col1)<>ascii(c.col2)
and ascii(a.col2)<>ascii(b.col1) and ascii(a.col2)<>ascii(b.col2) and ascii(a.col2)<>ascii(c.col1) and ascii(a.col2)<>ascii(c.col2)
and ascii(b.col1)<>ascii(c.col1) and ascii(b.col1)<>ascii(c.col2)
and ascii(b.col2)<>ascii(c.col1) and ascii(b.col2)<>ascii(c.col2)
and ascii(a.col1)=ascii('a')
group by a.col1,a.col2,b.col1,b.col2,c.col1,c.col2 drop table #t,t_a/*
col1 col2 col1 col2 col1 col2
---- ---- ---- ---- ---- ----
a A b B c C
a A c C B b
a b A B c C
a b B A C c
a b c C A B
a b c C B A
a c A C b B
a c b B A C
a c b B C A
a c C A b B
*/
create table t_a(old varchar(1),able bit)
insert t_a
select 'A',1 union all
select 'B',1 union all
select 'C',1 union all
select 'a',1 union all
select 'b',0 union all
select 'c',0select id=identity(int,1,1), col1=a.old,col2=b.old
into #t
from t_a a,t_a b
where a.able|b.able=1
and ascii(a.old)<>ascii(b.old)
and ((ascii(a.old)<ascii('Z') and ascii(b.old)<ascii('Z'))
or (ascii(upper(a.old))=ascii(upper(b.old)))
or (ascii(a.old)>=ascii('a') and ascii(b.old)>=ascii('a')))select a.col1,a.col2,b.col1,b.col2,c.col1,c.col2
from #t a,#t b,#t c
where a.id<>b.id and a.id<>c.id and b.id<>c.id
and ascii(a.col1)<>ascii(b.col1) and ascii(a.col1)<>ascii(b.col2) and ascii(a.col1)<>ascii(c.col1) and ascii(a.col1)<>ascii(c.col2)
and ascii(a.col2)<>ascii(b.col1) and ascii(a.col2)<>ascii(b.col2) and ascii(a.col2)<>ascii(c.col1) and ascii(a.col2)<>ascii(c.col2)
and ascii(b.col1)<>ascii(c.col1) and ascii(b.col1)<>ascii(c.col2)
and ascii(b.col2)<>ascii(c.col1) and ascii(b.col2)<>ascii(c.col2)
and ascii(a.col1)=ascii('a')
group by a.col1,a.col2,b.col1,b.col2,c.col1,c.col2 drop table #t,t_a/*
col1 col2 col1 col2 col1 col2
---- ---- ---- ---- ---- ----
a A b B c C
a A c C B b
a b A B c C
a b B A C c
a b c C A B
a b c C B A
a c A C b B
a c b B A C
a c b B C A
a c C A b B
*/
不明白你的意思
B
C
a 小老虎
b
c我是算的他们有几种过法的组合,不过结果还不是很理想,因为还有些重复数据
小老虎回来
A:大老虎、大狮子、大豹子、小老虎、小豹子
B:小狮子小老虎、小豹子过去
小老虎回来
A:大老虎、大狮子、大豹子、小老虎
B:小狮子、小豹子大狮子、大豹子过去
大狮子、小狮子回来
A:大老虎、大狮子、小老虎、小狮子
B:大豹子、小豹子大老虎、小老虎过去
大豹子、小豹子回来
A:大狮子、大豹子、小狮子、小豹子
B:大老虎、小老虎大狮子、大豹子过去
小老虎回来
A:小老虎、小狮子、小豹子
B:大老虎、大狮子、大豹子小老虎带小狮子过去
小老虎回来
A:小老虎、小豹子
B:大老虎、大狮子、大豹子、小狮子小老虎带小豹子过去
A:
B:大老虎、大狮子、大豹子、小老虎、小狮子、小豹子