MySQL求助：筛选出现至少3次的IP并分组查询返回0行

首先帮你理清为什么你的尝试返回0行：

• 当你用GROUP BY ip时，每个分组里的所有记录都属于同一个IP，所以COUNT(DISTINCT ip)的结果永远是1，根本满足不了>2的筛选条件，这就是返回空结果的核心原因。你需要统计的是每个IP对应的记录条数，而非去重后的IP数量，应该用COUNT(*)或者COUNT(ip)（确保ip不为空的情况下）。

下面给出两种符合你需求的解决方案：

方法一：子查询筛选IP后关联原数据 #

这种方法兼容性好，适合不支持窗口函数的老版本数据库：

SELECT t2.ip, t1.timestamp
FROM table1 t1
INNER JOIN table2 t2 ON t2.id = t1.t2id
WHERE t2.ip IN (
    -- 先从前1000条记录中筛选出出现至少3次的IP
    SELECT ip
    FROM (
        SELECT t2.ip
        FROM table1 t1
        INNER JOIN table2 t2 ON t2.id = t1.t2id
        ORDER BY t1.timestamp DESC LIMIT 1000
    ) AS tmp_records
    GROUP BY ip
    HAVING COUNT(*) >= 3
)
-- 按IP分组，同一IP下按时间戳降序排列
ORDER BY t2.ip, t1.timestamp DESC;

逻辑说明： #

1. 先获取原关联查询的前1000条记录（按时间降序）；
2. 统计这些记录中每个IP的出现次数，筛选出次数≥3的IP；
3. 再次关联原表，只保留这些符合条件的IP对应的记录，最后按要求排序输出。

方法二：窗口函数（推荐，更高效简洁） #

如果你的数据库支持窗口函数（比如MySQL 8.0+、PostgreSQL、SQL Server等），用这种方法更高效，不需要多次关联：

SELECT ip, timestamp
FROM (
    SELECT 
        t2.ip,
        t1.timestamp,
        -- 用窗口函数计算当前IP在前1000条记录中的总出现次数
        COUNT(*) OVER (PARTITION BY t2.ip) AS ip_occurrence
    FROM table1 t1
    INNER JOIN table2 t2 ON t2.id = t1.t2id
    ORDER BY t1.timestamp DESC LIMIT 1000
) AS tmp_with_count
-- 只保留出现次数≥3的IP记录
WHERE ip_occurrence >= 3
ORDER BY ip, timestamp DESC;

逻辑说明： #

1. 在获取前1000条记录的同时，用窗口函数COUNT(*) OVER (PARTITION BY t2.ip)给每条记录打上对应IP的总出现次数标签；
2. 直接筛选出次数≥3的记录，最后按IP和时间戳排序，得到你想要的输出格式。

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