我有一个名为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 digits
HAVING 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中有有效的手机号码