mysql - mysql表的SQL公式

问题描述

你好——我有一个数据库表（MySQL 版本 5.6.41-84.1-log），它有大约 92,000 个条目，列：

id（增量唯一 ID）
post_type（不重要）
post_id（不重要，但显示与另一个表的关系）
user_id（不重要）
投票（不重要）
ip（IP地址，即123.123.123.123）
已投票（格林威治标准时间的时间戳，即 2018-12-03 04:50:05）

我最近举办了一场比赛，我们有一个规则，即没有一个 IP 每天的投票次数不能超过 60 次。所以现在我需要运行一个应用以下规则的自定义 SQL 公式：

对于每个 IP 地址，如果有 > 60 行，则每天删除这些额外的行。

谢谢您的帮助！

标签： mysqlsql

这是一个复杂的问题，我认为如果没有实际的表格和数据可供使用，很难提供 100% 确定的答案。

但是，让我尝试描述逻辑，并逐步构建查询，以便您可以使用它并可能修复潜伏的错误。

1) 我们首先选择在某一天发布超过 60 票的所有 IP 地址。为此，我们group by在投票日和 IP 地址上使用 a，并结合having子句

select date(voted), ip_adress
from table 
group by date(voted), ip_adress 
having count(*) > 60

2）从那时起，我们回到表格，选择每个投票日/ip地址对对应的前60个id。id是一个自动递增的字段，所以我们只需使用该字段进行排序并使用 mysqllimit指令

    select id, ip_adress, date(voted) as day_voted
    from table 
    where ip_adress, date(voted) in (
        select date(voted), ip_adress 
        from table 
        group by date(voted), ip_adress 
        having count(*) > 60
    ) 
    order by id
    limit 60

3) 最后，我们再次回到表中，搜索所有 ip 地址和投票日期属于上述列表，但 id 大于列表最大 id 的 id。这是通过 a joinand requiresgroup by子句实现的。

select t1.id 
from 
    table t1
    join (      
        select id, ip_adress, date(voted) as day_voted 
        from table 
        where ip_adress, date(voted) in (
            select date(voted), ip_adress 
            from table
            group by date(voted), ip_adress
            having count(*) > 60
        )
        order by id
        limit 60
    ) t2 
        on t1.ip_adress = t2.ip_adress 
        and date(t1.voted) = t2.day_voted and t1.id > max(t2.id)
group by t1.id

这应该返回我们需要删除的所有 id 的列表。在你走得更远之前测试一下。

4）最后一步是删除这些ID。mysql 中存在一些限制，使得deletewith 子查询条件很难实现。有关技术背景的更多信息，请参阅以下 SO 问题。您可以使用临时表来存储选定的 id，或者尝试通过包装子查询并为其设置别名来超越 mysql。让我们尝试第二个选项：

delete t.* from table t where id in ( select id from (
    select t1.id 
    from 
        table t1
        join (      
            select id, ip_adress, date(voted) as day_voted 
            from table 
            where ip_adress, date(voted) in (
                select date(voted), ip_adress
                from table 
                group by date(voted), ip_adress
                having count(*) > 60
            )
            order by id
            limit 60
        ) t2 
            on t1.ip_adress = t2.ip_adress
            and date(t1.voted) = t2.day_voted
            and t1.id > max(t2.id)
    group by t1.id
) x );

希望这可以帮助！

mysql - mysql表的SQL公式

问题描述

解决方案

推荐阅读