我有一个名为contact的表,其中包含以下字段:
+----+-------+--------------+| ID | name | phone_no |+----+-------+--------------+
假设,我在此表中有以下值:
+----+-------+--------------+| ID | name | phone_no |+----+-------+--------------+| 1 | Alex | 9907661234 |--1,2 are | 2 | Alex | 09907661234 |--Same contacts but preceding with '0'| 3 | John | 9879612363 |--Same contacts but preceding with '91'| 4 | John | 919879612363 |-- 91 is (country code)| 5 | Shawn | 9979867123 |+----+-------+--------------+
我想找到重复数字的重复联系人数量(这里是前面的数字),0和91是重复的.
我想要以下输出:
+------------+-------------+| phone_no | cn |+------------+-------------+| 9879612363 | 2 || 9907661234 | 2 |+------------+-------------+
最佳答案假设您的电话号码是10个字符(正如您在问题中所示)并且可选地以某些代码为前缀.然后你可以在MysqL中使用RIGHT(str,len)
函数返回指定的最右边的字符数.查询如下(阅读评论):
SELECT RIGHT(`phone_no`,10) as `mobile`,-- selecting last 10 digits count(*) as `tatal_count`FROM `table_name`GROUP BY `mobile` -- group by last ten digitsHAVING count(`mobile`) > 1; -- if you want to select on duplicates
工作范例:
创建表格:
CREATE table IF NOT EXISTS `details` ( `ID` varchar(64) NOT NulL,`name` varchar(64) DEFAulT NulL,`phone` varchar(64) DEFAulT NulL,PRIMARY KEY (`ID`))
插入查询:
INSERT INTO `details` VALUES ("1","Alex","9907661234"),("2","09907661234"),("3","John","9879612363"),("4","919879612363"),("5","Shawn","9979867123");
[回答]
MysqL> SELECT RIGHT(`phone`,-> count(*) as `tatal_count` -> FROM `details` -> GROUP BY `mobile` -> ;+------------+-------------+| mobile | tatal_count |+------------+-------------+| 9879612363 | 2 || 9907661234 | 2 || 9979867123 | 1 |+------------+-------------+3 rows in set (0.04 sec)
假设您只想要那些重复的数字(不止一个),那么您可以在MysqL中使用HAVING子句:
MysqL> SELECT RIGHT(`phone`,-> count(*) as `tatal_count` -> FROM `details` -> GROUP BY `mobile` -> HAVING count(`mobile`) > 1;+------------+-------------+| mobile | tatal_count |+------------+-------------+| 9879612363 | 2 || 9907661234 | 2 |+------------+-------------+2 rows in set (0.00 sec)
我不检查代码是否正确,并假设您在DB中有有效的手机号码 总结
以上是内存溢出为你收集整理的mysql – 选择具有不同区号的重复电话号码的所有行?全部内容,希望文章能够帮你解决mysql – 选择具有不同区号的重复电话号码的所有行?所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)