如何在MySQL中查询30秒内base列重复超过3次的记录

咱们一步步来梳理这个问题，先看看原查询的几个待调整的点，再给出适配的解决方案：

原查询的核心问题 #

1. 时间范围是1分钟，需要改成你要的30秒区间
2. creation_date是varchar类型，直接用日期函数计算会出问题，必须先转成datetime类型才能正确做时间比较
3. 用SELECT *搭配GROUP BY base不符合SQL规范（MySQL非严格模式下能跑，但结果不可控），毕竟你要的是所有符合条件的记录，不是分组后的聚合结果

方案1：兼容所有MySQL版本的子查询写法 #

先通过子查询筛选出30秒内满足status='hot'且重复次数超3次的base值，再关联原表拿到完整记录：

SELECT ac.*
FROM allcoins ac
INNER JOIN (
    SELECT base
    FROM allcoins
    WHERE status = 'hot'
      -- 把字符串类型的creation_date转成datetime后再做时间比较
      AND STR_TO_DATE(creation_date, '%Y-%m-%d %H:%i:%s') > NOW() - INTERVAL 30 SECOND
    GROUP BY base
    HAVING COUNT(*) > 3
) AS qualified_bases ON ac.base = qualified_bases.base
WHERE ac.status = 'hot'
  AND STR_TO_DATE(ac.creation_date, '%Y-%m-%d %H:%i:%s') > NOW() - INTERVAL 30 SECOND;

方案2：MySQL 8.0+ 可用的窗口函数写法 #

如果你的MySQL版本是8.0及以上，用窗口函数会更简洁，直接计算每个base在目标时间窗口内的重复次数：

SELECT *
FROM (
    SELECT *,
           -- 按base分组，统计30秒内hot状态的记录数
           COUNT(*) OVER (
               PARTITION BY base
               WHERE status = 'hot'
                 AND STR_TO_DATE(creation_date, '%Y-%m-%d %H:%i:%s') > NOW() - INTERVAL 30 SECOND
           ) AS base_repeat_count
    FROM allcoins
    WHERE status = 'hot'
      AND STR_TO_DATE(creation_date, '%Y-%m-%d %H:%i:%s') > NOW() - INTERVAL 30 SECOND
) AS temp_result
WHERE base_repeat_count > 3;

关于测试数据的小说明 #

你的测试数据里，ICX有3条status='hot'的记录，时间都在30秒范围内，但目前数量是3次。如果想让这条数据被匹配到，可以把条件里的>3改成>=3，这样就能返回ICX的相关记录了。

另外，建议把creation_date字段类型改成datetime，这样不用每次查询都做类型转换，既能提升性能，还能避免字符串格式错误带来的问题。

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