我有一个名为contacts
的表,其中包含以下字段:
+----+-------+--------------+
| 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", "Alex", "09907661234"),
("3", "John", "9879612363"),
("4", "John", "919879612363"),
("5", "Shawn", "9979867123");
[ANSWER]
mysql> SELECT RIGHT(`phone`, 10) as `mobile`,
-> 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`, 10) as `mobile`,
-> 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)
我不检查代码是否正确,并假设您在数据库中有有效的手机号码