mysql - 选择所有具有相同 ips 的人

Question

有一张用户表users：

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

ETC...

必须用 1 选择所有用户ip。

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

我尝试过，它不起作用，不是我需要选择的。

标签： mysqlsql

Answer 1

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

对于 MySQL 5.x 使用

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