数据库 SQL SERVER 2000
create table TestTmp
(test varchar(20))
go
insert into TestTmp select '87888888'
insert into TestTmp select '88888888'
insert into TestTmp select '82348888'
insert into TestTmp select '82347777'
insert into TestTmp select '82349888'
insert into TestTmp select '87654321'
insert into TestTmp select '82349777'
insert into TestTmp select '82341231'
insert into TestTmp select '82347788'
insert into TestTmp select '13976548888'
insert into TestTmp select '13976541888'
insert into TestTmp select '13987654321'
insert into TestTmp select '13572348877'
insert into TestTmp select '13572344567'
insert into TestTmp select '13372344564'
如何查询test值3位尾数以上是一样的,如尾数是8888,7777,888,777?
如何查询test值4位以上尾数是顺序的,如87654321、1234567、1234、4321?
如何查询test值4位尾数是AABB,如8877,7766,6677?
create table TestTmp
(test varchar(20))
go
insert into TestTmp select '87888888'
insert into TestTmp select '88888888'
insert into TestTmp select '82348888'
insert into TestTmp select '82347777'
insert into TestTmp select '82349888'
insert into TestTmp select '87654321'
insert into TestTmp select '82349777'
insert into TestTmp select '82341231'
insert into TestTmp select '82347788'
insert into TestTmp select '13976548888'
insert into TestTmp select '13976541888'
insert into TestTmp select '13987654321'
insert into TestTmp select '13572348877'
insert into TestTmp select '13572344567'
insert into TestTmp select '13372344564'
如何查询test值3位尾数以上是一样的,如尾数是8888,7777,888,777?
如何查询test值4位以上尾数是顺序的,如87654321、1234567、1234、4321?
如何查询test值4位尾数是AABB,如8877,7766,6677?
(test varchar(20))
go
insert into TestTmp select '87888888'
insert into TestTmp select '88888888'
insert into TestTmp select '82348888'
insert into TestTmp select '82347777'
insert into TestTmp select '82349888'
insert into TestTmp select '87654321'
insert into TestTmp select '82349777'
insert into TestTmp select '82341231'
insert into TestTmp select '82347788'
insert into TestTmp select '13976548888'
insert into TestTmp select '13976541888'
insert into TestTmp select '13987654321'
insert into TestTmp select '13572348877'
insert into TestTmp select '13572344567'
insert into TestTmp select '13372344564'SELECT * FROM TESTTMP WHERE RIGHT(test,1)=LEFT(RIGHT(test,2),1) AND RIGHT(test,1)=LEFT(RIGHT(test,3),1) test
--------------------
87888888
88888888
82348888
82347777
82349888
82349777
13976548888
13976541888(所影响的行数为 8 行)
substring(reverse(test),2,1)=substring(reverse(test),3,1) and substring(reverse(test),2,1)=right(test,1)
WHERE RIGHT(test,1)*1=LEFT(RIGHT(test,2),1)*1-1
AND RIGHT(test,1)=LEFT(RIGHT(test,3),1)*1-2
AND RIGHT(test,1)=LEFT(RIGHT(test,4),1)*1-3test
--------------------
87654321
13987654321(所影响的行数为 2 行)
如何查询test值4位以上尾数是顺序的,如87654321、1234567、1234、4321?
如何查询test值4位尾数是AABB,如 8877,7766,6677?
go
create table TestTmp
(test varchar(20))
go
insert into TestTmp select '87888888'
insert into TestTmp select '88888888'
insert into TestTmp select '82348888'
insert into TestTmp select '82347777'
insert into TestTmp select '82349888'
insert into TestTmp select '87654321'
insert into TestTmp select '82349777'
insert into TestTmp select '82341231'
insert into TestTmp select '82347788'
insert into TestTmp select '13976548888'
insert into TestTmp select '13976541888'
insert into TestTmp select '13987654321'
insert into TestTmp select '13572348877'
insert into TestTmp select '13572344567'
insert into TestTmp select '13372344564'
go
select *
from testTmp
where
substring(reverse(test),1,1)=substring(reverse(test),2,1)
and
substring(reverse(test),1,1)=substring(reverse(test),3,1)
/*
test
--------------------
87888888
88888888
82348888
82347777
82349888
82349777
13976548888
13976541888*/
--如何查询test值3位尾数以上是一样的,如尾数是8888,7777,888,777
select *
from TestTmp
where replicate(RIGHT(test,1),3)=RIGHT(test,3)
/*
test
--------------------
87888888
88888888
82348888
82347777
82349888
82349777
13976548888
13976541888
*/
(test varchar(20))
go
insert into TestTmp select '87888888'
insert into TestTmp select '88888888'
insert into TestTmp select '82348888'
insert into TestTmp select '82347777'
insert into TestTmp select '82349888'
insert into TestTmp select '87654321'
insert into TestTmp select '82349777'
insert into TestTmp select '82341231'
insert into TestTmp select '82347788'
insert into TestTmp select '13976548888'
insert into TestTmp select '13976541888'
insert into TestTmp select '13987654321'
insert into TestTmp select '13572348877'
insert into TestTmp select '13572344567'
insert into TestTmp select '13372344564'
select * from TestTmp where right(test,1)=right(left(test,7),1) and
right(left(test,7),1)=right(left(test,6),1)test
--------------------
87888888
88888888
82348888
82347777
82349888
82349777(6 行受影响)
go
create table TestTmp
(test varchar(20))
go
insert into TestTmp select '87888888'
insert into TestTmp select '88888888'
insert into TestTmp select '82348888'
insert into TestTmp select '82347777'
insert into TestTmp select '82349888'
insert into TestTmp select '87654321'
insert into TestTmp select '82349777'
insert into TestTmp select '82341231'
insert into TestTmp select '82347788'
insert into TestTmp select '13976548888'
insert into TestTmp select '13976541888'
insert into TestTmp select '13987654321'
insert into TestTmp select '13572348877'
insert into TestTmp select '13572344567'
insert into TestTmp select '13372344564'
go
select *
from testTmp
where
substring(reverse(test),1,1)=substring(reverse(test),2,1)
and
substring(reverse(test),3,1)=substring(reverse(test),4,1)
/*
test
--------------------
87888888
88888888
82348888
82347777
82347788
13976548888
13572348877
*/
WHERE
RIGHT(test,1)=LEFT(RIGHT(test,2),1)
AND
LEFT(RIGHT(test,3),1)=LEFT(RIGHT(test,4),1)test
--------------------
87888888
88888888
82348888
82347777
82347788
13976548888
13572348877(所影响的行数为 7 行)
go
create table TestTmp
(test varchar(20))
go
insert into TestTmp select '87888888'
insert into TestTmp select '88888888'
insert into TestTmp select '82348888'
insert into TestTmp select '82347777'
insert into TestTmp select '82349888'
insert into TestTmp select '87654321'
insert into TestTmp select '82349777'
insert into TestTmp select '82341231'
insert into TestTmp select '82347788'
insert into TestTmp select '13976548888'
insert into TestTmp select '13976541888'
insert into TestTmp select '13987654321'
insert into TestTmp select '13572348877'
insert into TestTmp select '13572344567'
insert into TestTmp select '13372344564'
go
select *
from testTmp
where
(
substring(reverse(test),1,1)=substring(reverse(test),2,1)+1
and
substring(reverse(test),2,1)=substring(reverse(test),3,1)+1
and
substring(reverse(test),3,1)=substring(reverse(test),4,1)+1
)
or
(
substring(reverse(test),1,1)=substring(reverse(test),2,1)-1
and
substring(reverse(test),2,1)=substring(reverse(test),3,1)-1
and
substring(reverse(test),3,1)=substring(reverse(test),4,1)-1
)
/*
test
--------------------
87654321
13987654321
13572344567
*/
select * from TestTmp where
Ascii(right(test,1)) = Ascii(right(test,2)) and
Ascii(right(test,3)) = Ascii(right(test,4)) and
Ascii(right(test,2)) != Ascii(right(test,3))
select *
from TestTmp
where replicate(RIGHT(test,1),2)=RIGHT(test,2)
and SUBSTRING(REVERSE(test),3,1)=SUBSTRING(REVERSE(test),4,1)
and SUBSTRING(REVERSE(test),3,1)<>SUBSTRING(REVERSE(test),2,1)
/*
test
--------------------
82347788
13572348877*/
这个方法可以了。
(test varchar(20))
go
insert into TestTmp select '87888888'
insert into TestTmp select '88888888'
insert into TestTmp select '82348888'
insert into TestTmp select '82347777'
insert into TestTmp select '82349888'
insert into TestTmp select '87654321'
insert into TestTmp select '82349777'
insert into TestTmp select '82341231'
insert into TestTmp select '82347788'
insert into TestTmp select '13976548888'
insert into TestTmp select '13976541888'
insert into TestTmp select '13987654321'
insert into TestTmp select '13572348877'
insert into TestTmp select '13572344567'
insert into TestTmp select '13372344564'
select *
from TestTmp
where replicate(RIGHT(test,1),3)=RIGHT(test,3)
test
--------------------
87888888
88888888
82348888
82347777
82349888
82349777
13976548888
13976541888(8 行受影响)select * from TestTmp where abs(RIGHT(test,2)*1-LEFT(RIGHT(test,4),2)*1)=22test
--------------------
87654321
13987654321
13572344567(3 行受影响)
--4位顺序的select * from TestTmp where abs(RIGHT(test,4-1)*1-LEFT(RIGHT(test,4),4-1)*1)=REPLICATE(1,4-1)test
--------------------
87654321
13987654321
13572344567(3 行受影响)
--5位顺序的select * from TestTmp where abs(RIGHT(test,5-1)*1-LEFT(RIGHT(test,5),5-1)*1)=REPLICATE(1,5-1)test
--------------------
87654321
13987654321(2 行受影响)
0)Structure and datacreate table TestTmp
(test varchar(20))
go
insert into TestTmp select '87888888'
insert into TestTmp select '88888888'
insert into TestTmp select '82348888'
insert into TestTmp select '82347777'
insert into TestTmp select '82349888'
insert into TestTmp select '87654321'
insert into TestTmp select '82349777'
insert into TestTmp select '82341231'
insert into TestTmp select '82347788'
insert into TestTmp select '13976548888'
insert into TestTmp select '13976541888'
insert into TestTmp select '13987654321'
insert into TestTmp select '13572348877'
insert into TestTmp select '13572344567'
insert into TestTmp select '13372344564'
--SELECT * FROM TestTmp1)--如何查询test值3位尾数以上是一样的,如尾数是8888,7777,888,777?
SELECT * FROM
(
SELECT test,A = RIGHT(test,1), B= LEFT(RIGHT(test,2),1),C = LEFT(RIGHT(test,3),1) FROM TestTmp )D
WHERE D.A = D.B AND D.A = D.C AND D.B = D.Ctest A B C
-------------------- ---- ---- ----
87888888 8 8 8
88888888 8 8 8
82348888 8 8 8
82347777 7 7 7
82349888 8 8 8
82349777 7 7 7
13976548888 8 8 8
13976541888 8 8 8(8 row(s) affected)
2)--如何查询test值4位以上尾数是顺序的,如87654321、1234567、1234、4321?
SELECT * FROM
(
SELECT test,A = RIGHT(test,1), B= LEFT(RIGHT(test,2),1),C = LEFT(RIGHT(test,3),1),D = LEFT(RIGHT(test,4),1) FROM TestTmp )E
WHERE (E.A = E.B + 1) AND (E.B = E.C + 1) AND (E.C = E.D + 1)
OR ((E.A = E.B - 1) AND (E.B = E.C - 1) AND (E.C = E.D - 1))test A B C D
-------------------- ---- ---- ---- ----
87654321 1 2 3 4
13987654321 1 2 3 4
13572344567 7 6 5 4(3 row(s) affected)
3)--如何查询test值4位尾数是AABB,如8877,7766,6677?SELECT * FROM
(
SELECT test,A = RIGHT(test,1), B= LEFT(RIGHT(test,2),1),C = LEFT(RIGHT(test,3),1),D = LEFT(RIGHT(test,4),1) FROM TestTmp )E
WHERE (E.A = E.B ) AND (E.C = E.D ) AND (E.A <> E.C) test A B C D
-------------------- ---- ---- ---- ----
82347788 8 8 7 7
13572348877 7 7 8 8(2 row(s) affected)
where substr(字段,length(字段),1) = substr(字段,length(字段)-1,1)
and substr(字段,length(字段)-2,1) = substr(字段,length(字段)-3,1)
select t.mun from 表名 t
where substr(t.mun,length(t.mun),1) = substr(t.mun,length(t.mun)-1,1)
and substr(t.mun,length(t.mun)-2,1) = substr(t.mun,length(t.mun)-3,1)
找出全部尾数是AAAB的号码
select t.mun from 表名 t
where substr(t.mun,length(t.mun)-3,1) = substr(t.mun,length(t.mun)-1,1)
and substr(t.mun,length(t.mun)-2,1) = substr(t.mun,length(t.mun)-3,1)
and substr(t.mun,length(t.mun)-3,1) != substr(t.mun,length(t.mun),1)
找出全部尾数是ABAB的号码
select t.mun from 表名 t
where substr(t.mun,length(t.mun),1) = substr(t.mun,length(t.mun)-2,1)
and substr(t.mun,length(t.mun)-1,1) = substr(t.mun,length(t.mun)-3,1)
and substr(t.mun,length(t.mun)-1,1) != substr(t.mun,length(t.mun),1)
找出全部尾数是ABBA的号码
select t.mun from 表名 t
where substr(t.mun,length(t.mun),1) = substr(t.mun,length(t.mun)-3,1)
and substr(t.mun,length(t.mun)-2,1) = substr(t.mun,length(t.mun)-1,1)
and substr(t.mun,length(t.mun)-1,1) != substr(t.mun,length(t.mun),1)
N=0代表末位
1代表倒数第二位
以此类推
然后增加where条件即可
(mun)是字段名