sql语句结果过滤

2009-03-18 10:48:27 185 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

大数据之VIP（Virtual IP，虚拟IP）负载均衡 VIP（Virtual IP，虚拟IP）负载均衡是一种在计算机网络中常用的技术，用于将网络请求流量均匀地分散到多个服务器上，以提高系统的可扩展性、可靠性和性能。阅读详情

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

...全文

银河麒麟V10 SP1 x86_64架构下离线部署全栈开发环境：Java8+Mysql5.7+Tomcat8实战指南本文详细介绍了在银河麒麟V10 SP1 x86_64架构下离线部署全栈开发环境的实战指南，包括Java8、Mysql5.7和Tomcat8的安装与配置。通过步骤详解和常见问题排查，帮助开发者在国产操作系统上高效搭建稳定可靠的开发环境，特别适合政务系统和企业级应用部署。阅读详情

naner_china

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

playwarcraft 2009-03-18

打赏
举报



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

naner_china 2009-03-18

打赏
举报

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

dj3688 2009-03-18

打赏
举报

[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

lgx0914 2009-03-18

打赏
举报

这个方法最简单，速度最快
[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]

naner_china 2009-03-18

打赏
举报

刚才没说明白

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

dj3688 2009-03-18

打赏
举报

2楼的思路比较新颖 ~~

学习

ks_reny 2009-03-18

打赏
举报

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

让你望见影子的墙 2009-03-18

打赏
举报

--> (让你望见影子的墙)生成测试数据，时间：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

dawugui 2009-03-18

打赏
举报

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 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 2009-03-18

打赏
举报

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

sql语句 结果过滤

相关推荐

sql语句结果过滤