我有一个名为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   |
+----+-------+--------------+

我想查找重复的联系人的数量,其中091的重复编号(此处为前面的数字)是重复的。

我想要以下输出:
+------------+-------------+
| 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)

我不检查代码是否正确,并假设您在数据库中有有效的手机号码

10-05 21:28
查看更多