手机号码尾数优号选择
单字段mobile_number
判断格式
AAAA 如:15980021111
AAABBB 如:15980222111
AABBCC 如:15980221133
AAAAB 如:15980011112
等sql如何实现?
单字段mobile_number
判断格式
AAAA 如:15980021111
AAABBB 如:15980222111
AABBCC 如:15980221133
AAAAB 如:15980011112
等sql如何实现?
15980222111对应的是AAABBB
union all
select if(find_in_set('15980222111',number)>0,'AAABBB','')
.....................
select '15980222111' REGEXP '^[0-9]{5}([0-9])\\1{2}([0-9])\\2{2}$';#bad
select '15980221133' REGEXP '^[0-9]{5}([0-9])\\1([0-9])\\2([0-9])\\3$';#bad
select '15980011112' REGEXP '^[0-9]{6}([0-9])\\1{3}[0-9]$';#ok两个是对的,还有2个是错的,再研究.....
SELECT 'AAABBB',mobile_number,SUBSTRING(mobile_number,6,3) as a,Right(mobile_number,3) as b FROM t_konio t Having Replace(a,Right(a,1),'')='' And Replace(b,Right(b,1),'')='';
SELECT 'AABBCC',mobile_number,SUBSTRING(mobile_number,6,2) as a,SUBSTRING(mobile_number,8,2) as b,Right(mobile_number,2) as c FROM t_konio t Having Replace(a,Right(a,1),'')='' And Replace(b,Right(b,1),'')='' And Replace(c,Right(c,1),'')='';
SELECT 'AAAAB',mobile_number,SUBSTRING(mobile_number,7,4) as a FROM t_konio t Having Replace(a,Right(a,1),'')='';
再补充一下字段 mobile_number 内有下面数据
15980021111
15980022222
15980023333
15980024444
15980025555
15980222111优号的判断是
15980021111 尾数有四个相同1 所以是 AAAA类型优号 (指四个尾数相同,可能是0-9任意一个) 如果尾数五个相同 是 AAAA类型优号15980222111 尾数是3个相同2跟3个相同1 所以是AAABBB类型优号(指尾数两个不同数据连续相同3次)
mysql> select * from s;
+-------------+
| phone |
+-------------+
| 15980021111 |
| 15980022222 |
| 15980023333 |
| 15980024444 |
| 15980025555 |
| 15980022111 |
| 15980021422 |
+-------------+
7 rows in set (0.02 sec)mysql> select if(find_in_set(right(phone,3),
-> '000,111,222,333,444,555,666,777,888,999')>0,
-> if(find_in_set(right(phone,4),
-> '0000,1111,2222,3333,4444,5555,6666,7777,8888,9999')>0,'AAAA','AAABBB'),
-> '普通号') rank
-> from s;
+--------+
| rank |
+--------+
| AAAA |
| AAAA |
| AAAA |
| AAAA |
| AAAA |
| AAABBB |
| 普通号 |
+--------+
7 rows in set (0.00 sec)