There is a table of users users
:
x
+----+---------+------------+
| 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.
Advertisement
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 )