mysql> select * from t_huanfengzhizhuo; +-------------+ | numa | +-------------+ | 13544444449 | | 13688888858 | | 15820345678 | | 15975553333 | | 15975552222 | | 15975626666 | | 15820233333 | | 15820228888 | | 15920115555 | | 15920332222 | | 15920303333 | | 15920306666 | | 15920396666 | | 15920383333 | | 15920323333 | | 15920363333 | | 15920326666 | | 15818818888 | | 15818823333 | | 15920993333 | | 15920803333 | | 15920802222 | | 15920806666 | | 15918888887 | | 15920888880 | | 15920565688 | | 15920888889 | | 15925566886 | | 15922339995 | +-------------+ 29 rows in set (0.00 sec)mysql> select * from t_huanfengzhizhuo -> where -> (mid(numa,1,2)%11=0 and mid(numa,3,2)%11=0 and mid(numa,1,2)!=mid(numa,3,2)) or -> (mid(numa,2,2)%11=0 and mid(numa,4,2)%11=0 and mid(numa,2,2)!=mid(numa,4,2)) or -> (mid(numa,3,2)%11=0 and mid(numa,5,2)%11=0 and mid(numa,3,2)!=mid(numa,5,2)) or -> (mid(numa,4,2)%11=0 and mid(numa,6,2)%11=0 and mid(numa,4,2)!=mid(numa,6,2)) or -> (mid(numa,5,2)%11=0 and mid(numa,7,2)%11=0 and mid(numa,5,2)!=mid(numa,7,2)) or -> (mid(numa,6,2)%11=0 and mid(numa,8,2)%11=0 and mid(numa,6,2)!=mid(numa,8,2)) or -> (mid(numa,7,2)%11=0 and mid(numa,9,2)%11=0 and mid(numa,7,2)!=mid(numa,9,2)) or -> (mid(numa,8,2)%11=0 and mid(numa,10,2)%11=0 and mid(numa,8,2)!=mid(numa,10,2)) ; +-------------+ | numa | +-------------+ | 15975553333 | | 15975552222 | | 15820228888 | | 15920115555 | | 15920332222 | | 15920993333 | | 15925566886 | | 15922339995 | +-------------+ 8 rows in set (0.00 sec)mysql>
ABABmysql> select * from t_huanfengzhizhuo -> where -> (mid(numa,1,4)%101=0 and mid(numa,1,1)!=mid(numa,2,1)) or -> (mid(numa,2,4)%101=0 and mid(numa,2,1)!=mid(numa,3,1)) or -> (mid(numa,3,4)%101=0 and mid(numa,3,1)!=mid(numa,4,1)) or -> (mid(numa,4,4)%101=0 and mid(numa,4,1)!=mid(numa,5,1)) or -> (mid(numa,5,4)%101=0 and mid(numa,5,1)!=mid(numa,6,1)) or -> (mid(numa,6,4)%101=0 and mid(numa,6,1)!=mid(numa,7,1)) or -> (mid(numa,7,4)%101=0 and mid(numa,7,1)!=mid(numa,8,1)) or -> (mid(numa,8,4)%101=0 and mid(numa,8,1)!=mid(numa,9,1)); +-------------+ | numa | +-------------+ | 15920303333 | | 15920565688 | +-------------+ 2 rows in set (0.00 sec)mysql>
AAABBmysql> select * from t_huanfengzhizhuo -> where -> (mid(numa,1,3)%111=0 and mid(numa,4,2)%11=0 and mid(numa,1,1)!=mid(numa,4,1)) or -> (mid(numa,2,3)%111=0 and mid(numa,5,2)%11=0 and mid(numa,2,1)!=mid(numa,5,1)) or -> (mid(numa,3,3)%111=0 and mid(numa,6,2)%11=0 and mid(numa,3,1)!=mid(numa,6,1)) or -> (mid(numa,4,3)%111=0 and mid(numa,7,2)%11=0 and mid(numa,4,1)!=mid(numa,7,1)) or -> (mid(numa,5,3)%111=0 and mid(numa,8,2)%11=0 and mid(numa,5,1)!=mid(numa,8,1)) or -> (mid(numa,6,3)%111=0 and mid(numa,9,2)%11=0 and mid(numa,6,1)!=mid(numa,9,1)) or -> (mid(numa,7,3)%111=0 and mid(numa,10,2)%11=0 and mid(numa,7,1)!=mid(numa,10,1)); +-------------+ | numa | +-------------+ | 15975553333 | | 15975552222 | +-------------+ 2 rows in set (0.00 sec)
使用正则表达式可以非常方便的解决问题。 AABB: select * from t_huanfengzhizhuo where numa REGEXP '(11|22|33|44|55|66|77|88|99){2}' ABAB: select * from t_huanfengzhizhuo where numa REGEXP '(1[^1]|2[^2]|3[^3]|4[^4]|5[^5]|6[^6]|7[^7]|8[^8]|9[^9]){2}' AAABB: select * from t_huanfengzhizhuo where numa REGEXP '(111|222|333|444|555|666|777|888|999)(11|22|33|44|55|66|77|88|99)'
13544444449
13688888858
15820345678
15975553333
15975552222
15975626666
15820233333
15820228888
15920115555
15920332222
15920303333
15920306666
15920396666
15920383333
15920323333
15920363333
15920326666
15818818888
15818823333
15920993333
15920803333
15920802222
15920806666
15918888887
15920888880
15920565688
15920888889
15925566886
15922339995把里面有2233 5566 这种类型的号码筛选出来!
+-------------+
| numa |
+-------------+
| 13544444449 |
| 13688888858 |
| 15820345678 |
| 15975553333 |
| 15975552222 |
| 15975626666 |
| 15820233333 |
| 15820228888 |
| 15920115555 |
| 15920332222 |
| 15920303333 |
| 15920306666 |
| 15920396666 |
| 15920383333 |
| 15920323333 |
| 15920363333 |
| 15920326666 |
| 15818818888 |
| 15818823333 |
| 15920993333 |
| 15920803333 |
| 15920802222 |
| 15920806666 |
| 15918888887 |
| 15920888880 |
| 15920565688 |
| 15920888889 |
| 15925566886 |
| 15922339995 |
+-------------+
29 rows in set (0.00 sec)mysql> select * from t_huanfengzhizhuo
-> where
-> (mid(numa,1,2)%11=0 and mid(numa,3,2)%11=0 and mid(numa,1,2)!=mid(numa,3,2)) or
-> (mid(numa,2,2)%11=0 and mid(numa,4,2)%11=0 and mid(numa,2,2)!=mid(numa,4,2)) or
-> (mid(numa,3,2)%11=0 and mid(numa,5,2)%11=0 and mid(numa,3,2)!=mid(numa,5,2)) or
-> (mid(numa,4,2)%11=0 and mid(numa,6,2)%11=0 and mid(numa,4,2)!=mid(numa,6,2)) or
-> (mid(numa,5,2)%11=0 and mid(numa,7,2)%11=0 and mid(numa,5,2)!=mid(numa,7,2)) or
-> (mid(numa,6,2)%11=0 and mid(numa,8,2)%11=0 and mid(numa,6,2)!=mid(numa,8,2)) or
-> (mid(numa,7,2)%11=0 and mid(numa,9,2)%11=0 and mid(numa,7,2)!=mid(numa,9,2)) or
-> (mid(numa,8,2)%11=0 and mid(numa,10,2)%11=0 and mid(numa,8,2)!=mid(numa,10,2)) ;
+-------------+
| numa |
+-------------+
| 15975553333 |
| 15975552222 |
| 15820228888 |
| 15920115555 |
| 15920332222 |
| 15920993333 |
| 15925566886 |
| 15922339995 |
+-------------+
8 rows in set (0.00 sec)mysql>
-> where
-> (mid(numa,1,4)%101=0 and mid(numa,1,1)!=mid(numa,2,1)) or
-> (mid(numa,2,4)%101=0 and mid(numa,2,1)!=mid(numa,3,1)) or
-> (mid(numa,3,4)%101=0 and mid(numa,3,1)!=mid(numa,4,1)) or
-> (mid(numa,4,4)%101=0 and mid(numa,4,1)!=mid(numa,5,1)) or
-> (mid(numa,5,4)%101=0 and mid(numa,5,1)!=mid(numa,6,1)) or
-> (mid(numa,6,4)%101=0 and mid(numa,6,1)!=mid(numa,7,1)) or
-> (mid(numa,7,4)%101=0 and mid(numa,7,1)!=mid(numa,8,1)) or
-> (mid(numa,8,4)%101=0 and mid(numa,8,1)!=mid(numa,9,1));
+-------------+
| numa |
+-------------+
| 15920303333 |
| 15920565688 |
+-------------+
2 rows in set (0.00 sec)mysql>
-> where
-> (mid(numa,1,3)%111=0 and mid(numa,4,2)%11=0 and mid(numa,1,1)!=mid(numa,4,1)) or
-> (mid(numa,2,3)%111=0 and mid(numa,5,2)%11=0 and mid(numa,2,1)!=mid(numa,5,1)) or
-> (mid(numa,3,3)%111=0 and mid(numa,6,2)%11=0 and mid(numa,3,1)!=mid(numa,6,1)) or
-> (mid(numa,4,3)%111=0 and mid(numa,7,2)%11=0 and mid(numa,4,1)!=mid(numa,7,1)) or
-> (mid(numa,5,3)%111=0 and mid(numa,8,2)%11=0 and mid(numa,5,1)!=mid(numa,8,1)) or
-> (mid(numa,6,3)%111=0 and mid(numa,9,2)%11=0 and mid(numa,6,1)!=mid(numa,9,1)) or
-> (mid(numa,7,3)%111=0 and mid(numa,10,2)%11=0 and mid(numa,7,1)!=mid(numa,10,1));
+-------------+
| numa |
+-------------+
| 15975553333 |
| 15975552222 |
+-------------+
2 rows in set (0.00 sec)
AABB:
select * from t_huanfengzhizhuo
where numa REGEXP '(11|22|33|44|55|66|77|88|99){2}'
ABAB:
select * from t_huanfengzhizhuo
where numa REGEXP '(1[^1]|2[^2]|3[^3]|4[^4]|5[^5]|6[^6]|7[^7]|8[^8]|9[^9]){2}'
AAABB:
select * from t_huanfengzhizhuo
where numa REGEXP '(111|222|333|444|555|666|777|888|999)(11|22|33|44|55|66|77|88|99)'