在mysql中查找2个变量(mybe唯一值)我找不到更好的标题

Question

谁知道怎么才能找到？我有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，我怎么能从结果中删除坑呢？

Answer 1

尝试以下操作-

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`);

Answer 2

SELECT distinct `username`, `ip` 
FROM `logs`
WHERE `username` 
  IN (SELECT `username`
      FROM `logs`
      GROUP BY `username`
      HAVING COUNT(*) >1)

Answer 3

尝尝这个

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