Mysql查找删除重复行

-- 准备测试数据库和数据表，并写入初始数据
mysql> create database demo;
mysql> use demo;
mysql> create table mytable (id int not null primary key,  day date not null);
mysql> insert into mytable(id, day) values(1, "2018-04-14");
mysql> insert into mytable(id, day) values(2, "2018-04-15");
mysql> insert into mytable(id, day) values(3, "2018-04-16");
mysql> insert into mytable(id, day) values(4, "2018-04-16");
mysql> insert into mytable(id, day) values(5, "2018-04-15");
mysql> insert into mytable(id, day) values(6, "2018-04-16");
mysql> select * from mytable;
+----+------------+
| id | day        |
+----+------------+
|  1 | 2018-04-14 |
|  2 | 2018-04-15 |
|  3 | 2018-04-16 |
|  4 | 2018-04-16 |
|  5 | 2018-04-15 |
|  6 | 2018-04-16 |
+----+------------+

-- 分组查询，计算每个 day 的数量
mysql> select day, count(*) from mytable group by day;
+------------+----------+
| day        | count(*) |
+------------+----------+
| 2018-04-14 |        1 |
| 2018-04-15 |        2 |
| 2018-04-16 |        3 |
+------------+----------+
-- 只显示相同 day 数量大于 1 的数据
mysql> select day, count(*) from mytable group by day having count(*)>1;
+------------+----------+
| day        | count(*) |
+------------+----------+
| 2018-04-15 |        2 |
| 2018-04-16 |        3 |
+------------+----------+

-- 创建临时表，并插入 id 最小的记录
mysql> create temporary table temptable(day date not null, min_id int not null);
mysql> insert into temptable(day, min_id) select day, min(id) from mytable group by day having count(*)>1;
mysql> select * from temptable;
+------------+--------+
| day        | min_id |
+------------+--------+
| 2018-04-15 |      2 |
| 2018-04-16 |      3 |
+------------+--------+

-- 删除 id 不在临时表中 day 重复的记录，即保留了相同 day 值，id 较小的记录
mysql> delete from mytable where exists(
    -> select * from temptable where temptable.day = mytable.day and temptable.min_id <> mytable.id);
mysql> select * from mytable;
+----+------------+
| id | day        |
+----+------------+
|  1 | 2018-04-14 |
|  2 | 2018-04-15 |
|  3 | 2018-04-16 |
+----+------------+