sql语句 结果过滤

[naner_china]

比如
201，
210，
102，
120，
012，
021，
123，
132，
213，
231，
312，
321，
055，
505，
550
条件1：三个数各不相同
结果:012,123
条件2：三个数中有任意两个相同
结果055

[dawugui]

--条件1：三个数各不相同 

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 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)

[等不到来世]

if object_id('[tb]') is not null drop table [tb]
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 行受影响)
*/

[dawugui]

create table tb(id varchar(10))
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

[让你望见影子的墙]

--> (让你望见影子的墙)生成测试数据，时间：2009-03-18

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

[ks_reny]

[Quote=引用 4 楼 HEROWANG 的回复:]
--> (让你望见影子的墙)生成测试数据，时间：2009-03-18

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
sele…
[/Quote]
頂 這個通俗易懂.

[dj3688]

2楼的思路比较新颖 ~~

学习

[naner_china]

刚才没说明白

组合的结果要过滤下
条件1：比如 012，021，102，120，201，210 只能取最小的这个 012
条件2 只能取最小的 055

[lgx0914]

这个方法最简单，速度最快
[Quote=引用 2 楼 szx1999 的回复:]
SQL codeif object_id('[tb]') is not null drop table [tb]
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…
[/Quote]

[dj3688]

[Quote=引用 7 楼 naner_china 的回复:]
刚才没说明白

组合的结果要过滤下
条件1：比如 012，021，102，120，201，210 只能取最小的这个 012
条件2 只能取最小的 055
[/Quote]
参鉴用2楼的回复
--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

[naner_china]

结果不是想要的 ，先结贴 再重发

[playwarcraft]

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