34,591
社区成员
发帖
与我相关
我的任务
分享
create table tb(字段 varchar(10))
insert into tb select '12312142'
insert into tb select '11415151'
insert into tb select '15151511'
insert into tb select '11212432'
insert into tb select '14141414'
insert into tb select '14141414'
insert into tb select '12414543'
insert into tb select '14198210'
go
select * from tb where right(字段,3)=rtrim(right(字段,1)+2)+rtrim(right(字段,1)+1)+right(字段,1)
/*
字段
----------
11212432
12414543
14198210
create table tb(col varchar(10))
insert into tb select '12312142'
insert into tb select '11415151'
insert into tb select '15151511'
insert into tb select '11212432'
insert into tb select '14141414'
insert into tb select '14141414'
insert into tb select '12414543'
insert into tb select '14198210'
go
select * from tb where substring(col,6,1)=substring(col,7,1)+1 and substring(col,7,1)=substring(col,8,1)+1
/*
col
----------
11212432
12414543
14198210
(3 行受影响)
*/
go
drop table tb
某字段纯数字组成的字符串。有没有办法快速取出此字段的后三位数字是递减的?
如:有记录
12312142
11415151
15151511
11212432
14141414
14141414
12414543
14198210
能否有办法快速取出 标红的三条记录?
create table #tb(col int)
insert #tb
select 12312142 union all
select 11415151 union all
select 15151511 union all
select 11212432 union all
select 14141414 union all
select 14141414 union all
select 12414543 union all
select 14198210
select * from #tb
where substring(cast(col as varchar(10)),len(col)-2,1)>substring(cast(col as varchar(10)),len(col)-1,1)
and substring(cast(col as varchar(10)),len(col)-1,1)>substring(cast(col as varchar(10)),len(col),11)
/*
col
-----------
11212432
12414543
14198210
(所影响的行数为 3 行)
*/
select * from tb right(字段,3)=rtrim(right(字段,1)+2)+rtrim(right(字段,1)+1)right(字段,1)