Select everyone with the same ips

There is a table of users users:

+----+---------+------------+  
| id |  login  |      ip    |
+----+---------+------------+  
|  1 |  user1  |  127.0.0.1 |  
|  2 |  user2  |  127.0.0.1 |  
+----+---------+------------+ 

ETC…

It is necessary to select all users with 1 ip.

SELECT u1.id, u1.login, u1.ip FROM users AS u1 LEFT JOIN users as u2 ON u1.ip = u2.ip;

I tried to do it, it doesn’t work, not what I need to choose.

Answer

WITH cte AS ( SELECT *, COUNT(*) OVER (PARTITION BY ip) cnt
              FROM source_table )
SELECT * FROM cte WHERE cnt > 1

For MySQL 5.x use

SELECT *
FROM source_table t1
WHERE EXISTS ( SELECT NULL
               FROM source_table t2
               WHERE t1.ip = t2.ip 
                 AND t1.id != t2.id )