問題描述

數據如下 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;