请教一个mysql去重取最新记录

问题描述

数据如下 id domain port email type name value ttl route def remark2390 test.cn 80 123@qq.com 1 www 123.123.123.123 0 0 0 2523 test.cn 80 123@qq.com 1 www 123.123.123.123 1800 0 0

一个表中有 2 条相同的数据，（除了 ttl 和 id 不一样外），现在要保留最新的一条记录（如 2523 ），请教如何操作

下面是需要用到的测试语句； 数据库是 mysql 5.7

use test;CREATE TABLE test ( `id` int(11) NOT NULL AUTO_INCREMENT, `domain` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `port` varchar(6) COLLATE utf8_unicode_ci DEFAULT ’80’, `email` varchar(45) COLLATE utf8_unicode_ci NOT NULL, `type` tinyint(4) DEFAULT NULL, `name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL, `value` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `ttl` int(11) DEFAULT ’0’, `route` tinyint(4) DEFAULT ’0’, `def` tinyint(1) DEFAULT ’0’, `remark` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2721 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ciINSERT INTO test.test (`id`, `domain`, `port`, `email`, `type`, `name`, `value`, `ttl`, `route`, `def`, `remark`) VALUES (’2390’, ’www.test.cn’, ’80’, ’123@qq.com’, ’1’, ’www’, ’123.123.123.123’, ’0’, ’0’, ’0’, NULL);INSERT INTO test.test (`id`, `domain`, `port`, `email`, `type`, `name`, `value`, `ttl`, `route`, `def`, `remark`) VALUES (’2523’, ’www.test.cn’, ’80’, ’123@qq.com’, ’1’, ’www’, ’123.123.123.123’, ’1800’, ’0’, ’0’, NULL);

问题解答

踩我的出来指出错误呀，让我学习学习哪里有问题。delete from testwhere (id,domain, port, email, type, name, value,route, def, remark) not in (select * from (select max(id) id,domain, port, email, type, name, value,route, def, remarkfrom test group by domain, port, email, type, name, value,route, def, remark) tmp)

select max(id),domain, port, email, type, name, value,route, def, remark from test GROUP BY domain, port, email, type, name, value,route, def, remark;

给你个生成删除脚本的sql

select concat('delete from test where id=',min(id),';') into outfile ’/tmp/delete_test.sql’ lines terminated by ’n’ from test group by domain, port, email, type, name, value,route, def, remark;