比如
201，
210，
102，
120，
012，
021，
123，
132，
213，
231，
312，
321，
055，
505，
550
条件1：三个数各不相同
结果:012,123
条件2：三个数中有任意两个相同
结果055
201，
210，
102，
120，
012，
021，
123，
132，
213，
231，
312，
321，
055，
505，
550
条件1：三个数各不相同
结果:012,123
条件2：三个数中有任意两个相同
结果055
(select left(id,1) id1 from tb ) t1,
(select substring(id,2,1) id2 from tb ) t2
(select right(id,1) id3 from tb ) t3
where t1.id1 <> t2.id2 and t2.id2 <> t3.id3 and t1.id1 <> t3.id3--条件2：三个数中有任意两个相同 select t1.id1 + t2.id2 + t3.id3 from
(select left(id,1) id1 from tb ) t1,
(select substring(id,2,1) id2 from tb ) t2
(select right(id,1) id3 from tb ) t3
where (t1.id1 = t2.id2 and t1.id1 <> t3.id3) or
(t1.id1 = t3.id3 and t1.id1 <> t2.id2) or
(t2.id2 = t3.id3 and t2.id2 <> t1.id1)
go
create table [tb]([col] varchar(3))
insert [tb]
select '201' union all
select '210' union all
select '102' union all
select '120' union all
select '012' union all
select '021' union all
select '123' union all
select '132' union all
select '213' union all
select '231' union all
select '312' union all
select '321' union all
select '055' union all
select '505' union all
select '550'
--1.
select * from [tb]
where len(replace(col,left(col,1),''))=2
and len(replace(col,right(col,1),''))=2
/*
col
----
201
210
102
120
012
021
123
132
213
231
312
321(12 行受影响)
*/--2.
select * from [tb]
where len(replace(col,left(col,1),''))=1
or len(replace(col,right(col,1),''))=1
/*
col
----
055
505
550(3 行受影响)
*/
insert into tb values('201')
insert into tb values('210')
insert into tb values('102')
insert into tb values('120')
insert into tb values('012')
insert into tb values('021')
insert into tb values('123')
insert into tb values('132')
insert into tb values('213')
insert into tb values('231')
insert into tb values('312')
insert into tb values('321')
insert into tb values('055')
insert into tb values('505')
insert into tb values('550')
go--条件1：三个数各不相同 select distinct t1.id1 + t2.id2 + t3.id3 from
(select left(id,1) id1 from tb ) t1,
(select substring(id,2,1) id2 from tb ) t2,
(select right(id,1) id3 from tb ) t3
where t1.id1 <> t2.id2 and t2.id2 <> t3.id3 and t1.id1 <> t3.id3 and t1.id1 + t2.id2 + t3.id3 in (select id from tb)
/*
------
012
021
102
120
123
132
201
210
213
231
312
321（所影响的行数为 12 行）
*/--条件2：三个数中有任意两个相同 select distinct t1.id1 + t2.id2 + t3.id3 from
(select left(id,1) id1 from tb ) t1,
(select substring(id,2,1) id2 from tb ) t2,
(select right(id,1) id3 from tb ) t3
where ((t1.id1 = t2.id2 and t1.id1 <> t3.id3) or
(t1.id1 = t3.id3 and t1.id1 <> t2.id2) or
(t2.id2 = t3.id3 and t2.id2 <> t1.id1)) and t1.id1 + t2.id2 + t3.id3 in (select id from tb)/*
------
055
505
550（所影响的行数为 3 行）*/drop table tb
if not object_id('tb') is null
drop table tb
Go
Create table tb([num] nvarchar(3))
Insert tb
select N'201' union all
select N'210' union all
select N'102' union all
select N'120' union all
select N'012' union all
select N'021' union all
select N'123' union all
select N'132' union all
select N'213' union all
select N'231' union all
select N'312' union all
select N'321' union all
select N'055' union all
select N'505' union all
select N'550'
Go
Select * from tb
select * from (
select 类型='三个数各不相同',num=case when left(num,1)<>substring(num,2,1) and left(num,1)<>right(num,1) and substring(num,2,1) <>right(num,1) then num end
from tb
union all
select 类型='两个数相同',num=case when left(num,1)=substring(num,2,1) or left(num,1)=right(num,1) or substring(num,2,1)=right(num,1) then num end
from tb)K
where num is not null三个数各不相同 201
三个数各不相同 210
三个数各不相同 102
三个数各不相同 120
三个数各不相同 012
三个数各不相同 021
三个数各不相同 123
三个数各不相同 132
三个数各不相同 213
三个数各不相同 231
三个数各不相同 312
三个数各不相同 321
两个数相同 055
两个数相同 505
两个数相同 550
--1.
select min(*) from [tb]
where len(replace(col,left(col,1),''))=2
and len(replace(col,right(col,1),''))=2--2.
select min(*) from [tb]
where len(replace(col,left(col,1),''))=1
or len(replace(col,right(col,1),''))=1
Create table T(col varchar(03))
insert into T select '201'
insert into T select '210'
insert into T select '102'
insert into T select '120'
insert into T select '012'
insert into T select '021'
insert into T select '213'
insert into T select '123'
insert into T select '055'
insert into T select '505'
insert into T select '550'GO
/*三個數各不相同*/
select distinct
case when b>c and a>c then c
else case when a>b and c>b then b
else a end
end as A,
case when (a>b and b>c) or (c>b and b>a) then b
else case when (b>a and a>c) or (c>a and a>b) then a
else c end
end as B,
case when a>b and a>c then a
else case when b>a and b>c then b
else c end
end as C
from
(
select substring(rtrim(col),1,1) as a,
substring(rtrim(col),2,1) as b,
substring(rtrim(col),3,1) as c
from T
) A
where a<>b and a<>c and b<>c/*
A B C
---------------------
0 1 2
1 2 3*//*有2個數相同*/
select distinct
case when a=b and a<c then a
else case when a=b and a>c then c
else case when b=c and a>b then b
else case when b=c and a<b then a
else case when a=c and a<b then a else b end
end
end
end
end as A ,
case when a=b then a
else c end as B,
case when a=b and a<c then c
else case when a=b and a>c then a
else case when b=c and a>b then a
else case when b=c and a<b then b
else case when a=c and a>b then a else b end
end
end
end
end as C
from
(
select substring(rtrim(col),1,1) as a,
substring(rtrim(col),2,1) as b,
substring(rtrim(col),3,1) as c
from T
) A
where (a=b and a<>c)
or (a=c and a<>b)
or (b=c and a<>b)/*
A B C
-------------------
0 5 5*/GO
drop table T