主页 / dba / 问题 / 28129
Accepted
icefex
Asked: 2012-11-04 07:56:53 +0800 CST

如何在给定时间范围内删除重复条目？

  • 772

我的表如下所示：

+-----------+
|     id    | <- primary key
|    time   | <- DateTime
|    row2   | <- varchar
|    row3   |
|    ...    |
+-----------+

我想删除所有条目，除了一个，它们在一整小时的时间范围内并且具有相同的 row2 值（不同的 row2）。是否可以在没有存储过程和/或非 sql 代码的情况下执行此操作？

mysql

2 个回答

  1. Best Answer

    此问题的通常解决方案是首先查找要保留的行，然后删除所有其他行。第一部分可以通过聚合和分组来完成：

    SELECT min(id)
FROM my_table
GROUP BY row2, DATE_FORMAT(`time`, '%Y-%m-%d %H')

    然后你可以写你的DELETE：

    DELETE FROM my_table
WHERE id NOT IN (
    SELECT min(id)
    FROM my_table
    GROUP BY row2, DATE_FORMAT(`time`, '%Y-%m-%d %H')
);

    这个主题有一些变化：您可以使用NOT EXISTS构造代替NOT IN（进行适当的更改），或使用USING关键字连接。

    • 4

  2. 早在 2011 年 2 月 22 日，我回答了一个关于使用 WHERE 子句针对自身的 DELETE 查询的问题：（MySQL 子查询问题）。

    这是一种更安全的方法：

    • 创建一个名为 mytable_key 的临时表
    • 将每小时每行 2 的最小 id 的键收集到 mytable_key
    • 创建一个名为 mytable_new 的临时表
    • 将 mytable_key 中的所有键与 mytable 连接并将其加载到 mytable_new
    • 将 mytable 重命名为 mytable_old
    • 将 mytable_new 重命名为 mytable
    • 删除 mytable_old
    • 删除 mytable_key

    这是实际的 SQL：

    CREATE TABLE mytable_key
    SELECT id FROM mytable
    WHERE 1=2
;
INSERT INTO mytable_key
    SELECT MIN(id) FROM mytable
    GROUP BY row2,DATE_FORMAT(`time`,'%Y%m%d%H')
;
ALTER TABLE mytable_key ADD PRIMARY KEY (id);
CREATE TABLE mytable_new LIKE mytable;
INSERT INTO mytable_new
    SELECT B.* FROM mytable_key A
    INNER JOIN mytable B USING (id)
;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;
DROP TABLE mytable_key;

    您可以按原样运行这些命令，也可以将它们放在存储过程中。

    虽然我给dezso 的答案一个 +1，因为它在 Oracle、PostgreSQL 和 SQL Server 中机械地工作，从 MySQL DBA 中获取，但我不相信MySQL在对自己的表执行 DELETE 时通过转换来优化内部 SQL 的方法。这就是我提交此答案的原因。

    • 2

相关问题