任何人都知道如何找到？我有3列
1-id
2-用户名
3-ip
样本数据为：
+----+-----------+----------------+
| id | username | ip |
+----+-----------+----------------+
| 1 | John | 127.0.0.1 |
| 2 | John | 127.0.0.1 |
| 3 | John | 127.0.0.1 |
| 4 | John | 127.0.0.1 |
| 5 | John | 127.0.0.1 |
| 6 | John | 127.0.0.2 |
| 7 | John | 127.0.0.2 |
| 8 | John | 127.0.0.3 |
| 9 | John | 127.0.0.1 |
| 10 | John | 127.0.0.1 |
| 11 | John | 127.0.0.1 |
| 12 | Pit | 127.1.1.1 |
| 13 | Pit | 127.1.1.1 |
| 14 | Pit | 127.1.1.1 |
| 15 | Pit | 127.1.1.1 |
| 16 | Pit | 127.1.1.1 |
| 17 | Pit | 127.1.1.1 |
+----+-----------+----------------+
所以我想找到任何有价值的重复ip在更好的情况下，我想找到唯一的值，这样通过这个查询我可以找到它：
SELECT distinct `username`, `ip` FROM `logs`
现在查询结果是
+----+-----------+----------------+
| id | username | ip |
+----+-----------+----------------+
| 1 | John | 127.0.0.1 |
| 7 | John | 127.0.0.2 |
| 8 | John | 127.0.0.3 |
| 12 | Pit | 127.1.1.1 |
+----+-----------+----------------+
这很好，但我真正想要的是什么？我想找到任何用户名有重复的ip，所以结果必须：
+----+-----------+----------------+
| id | username | ip |
+----+-----------+----------------+
| 1 | John | 127.0.0.1 |
| 7 | John | 127.0.0.2 |
| 8 | John | 127.0.0.3 |
+----+-----------+----------------+
因为在那里没有重复的ip我怎样才能从结果中去除坑？
尝试以下操作-
SELECT `logs`.`username`, `logs`.`ip`
FROM (
SELECT `username`
FROM `logs`
GROUP BY `username`
HAVING COUNT(DISTINCT `ip`) > 1
) AS `dups`
INNER JOIN `logs`
ON `dups`.`username` = `logs`.`username`
GROUP BY `logs`.`username`, `logs`.`ip`;
UPDATE随着数据集的增长，在用户名和ip上添加索引会对该查询的性能产生很大影响-
CREATE INDEX `IX_username_ip` ON `logs` (`username`, `ip`);
SELECT distinct `username`, `ip`
FROM `logs`
WHERE `username`
IN (SELECT `username`
FROM `logs`
GROUP BY `username`
HAVING COUNT(*) >1)
尝试这个
SELECT l1.username, l1.ip FROM logs l1
INNER JOIN logs l2
ON l2.username = l1.username
AND l2.ip != l1.ip
GROUP BY l1.username, l1.ip
select distinct username,group_concat( distinct ip ) ,count(distinct ip) as cnt from table_name group by username having cnt>1;