mysql - 仅在另一列具有不同值的情况下查找重复数据

Question

我有一个包含 IP 地址、成员(member) ID 和设备 ID 的表格。我想查找多个成员使用相同 IP 地址的帐户。但是，因为该表还记录了设备 ID，所以我不能只搜索重复的 IP 条目。

表格示例

device_key      member_id       ip_address  
123452345       1               127.0.0.1       
2687924         1               127.0.0.1       
3983456         23              255.255.255.255  
4659824         876             255.255.255.255
54653787        90              1.1.1.1      

我已经试过了:

SELECT 
    member_id,
    ip_address, 
    COUNT(ip_address)
FROM
    table
GROUP BY ip_address
HAVING COUNT(ip_address) > 1

以上代码将输出 127.0.0.1 和 255.255.255.255。但是我只对 255.255.255.255 感兴趣，因为这个 IP 被不止一个人使用。我想不出只输出这个的正确代码。

Answer 1

SELECT ip_address, COUNT(DISTINCT member_id)
FROM table
GROUP BY ip_address
HAVING COUNT(DISTINCT member_id) > 1;

如果你想得到使用这些IP地址的实际成员(member)ID，你可以这样做:

SELECT ip_address, member_id
FROM table
WHERE ip_address IN (SELECT ip_address
                     FROM table
                     GROUP BY ip_address
                     HAVING COUNT(DISTINCT member_id) > 1)
ORDER BY ip_address, member_id;