如何编写SQL语句筛选对应多个不同ID的IP分组数据

现有一张包含id和ip字段的数据表，数据如下：

期望查询结果剔除那些仅对应单个ID的IP数据（比如ip=3只对应id=5，所以需排除），得到如下结果：

我尝试了以下SQL语句，但没有效果：

select distinct poster_id, poster_ip from
`phpbbkp_posts` where poster_id in
(select poster_id 
from
(select distinct poster_id, poster_ip from `phpbbkp_posts`) p  
group by poster_id 
having count(*) > 1)
order by poster_id

解决方案 #

你的SQL逻辑方向错误：原语句是在找单个ID对应多个不同IP的记录，而需求是找单个IP对应多个不同ID的记录。

可以通过以下两种方式实现需求：

方式一：嵌套子查询

SELECT DISTINCT id, ip
FROM `phpbbkp_posts`
WHERE ip IN (
    -- 先筛选出对应至少2个不同ID的IP
    SELECT ip
    FROM `phpbbkp_posts`
    GROUP BY ip
    HAVING COUNT(DISTINCT id) > 1
)
ORDER BY id;

方式二：JOIN关联查询（性能更优）

SELECT DISTINCT p.id, p.ip
FROM `phpbbkp_posts` p
JOIN (
    -- 筛选符合条件的IP列表
    SELECT ip
    FROM `phpbbkp_posts`
    GROUP BY ip
    HAVING COUNT(DISTINCT id) > 1
) valid_ips ON p.ip = valid_ips.ip
ORDER BY p.id;

逻辑说明 #

1. 内层子查询通过GROUP BY ip分组，用COUNT(DISTINCT id) > 1筛选出对应多个不同ID的IP；
2. 外层查询基于这些IP，从原表中取出去重后的id和ip组合，就是最终需要的结果。

内容的提问来源于stack exchange，提问作者Marc A