mysql - 选择具有重复数据的行

Question

我有一些带有冗余数据的 MySQL 表需要删除。例如:

 id email            date       data...
 1  email1@gmail.com 2012-01-01 my_data
 2  email2@gmail.com 2012-01-01 my_data
 3  email1@gmail.com 2012-01-02 my_data
 4  email1@gmail.com 2012-01-02 my_data   (redundant)
 5  email2@gmail.com 2012-01-02 my_data

我需要删除多余的行，但我想先选择它们。我在 StackOverflow 上找到了这个，但它需要电子邮件地址

SELECT * 
FROM `my_table`
WHERE `id` IN (SELECT `id` 
               FROM `my_table` 
               where `email` = 'email1@gmail.com' 
               group by `date` 
               HAVING count(*) > 1)

我可以像上面那样使用什么查询，它不在嵌入式查询中使用 WHERE 限定符，所以我可以使用所有电子邮件地址？

查询可以是 SELECT 查询。我不介意在 PHPMyAdmin 中手动删除行。

Answer 1

DELETE FROM tableName
WHERE ID NOT IN
(
    SELECT minID
    FROM
    (
        SELECT email, date, MIN(id) minID
        FROM tableNAme
        GROUP BY email, date
    ) x
)

• SQLFiddle Demo

或使用JOIN

DELETE a 
FROM tableName a
    LEFT JOIN (
            SELECT minID
            FROM (
                    SELECT email, DATE, MIN(id) minID
                    FROM tableNAme
                    GROUP BY email, DATE
                    ) y
            ) x
            ON a.ID = x.minID
WHERE x.minID IS NULL;

• SQLFiddle Demo

以下查询仅为每个 email 和 date 复制了 SELECT 行

SELECT a.*
FROM tableName a
        LEFT JOIN 
       ( 
         SELECT minID
        FROM
        (
          SELECT email, date, MIN(id) minID
          FROM tableNAme
          GROUP BY email, date
        )y
       ) x ON a.ID = x.minID
WHERE x.minID IS NULL

• SQLFiddle Demo