字段 类型ID bigint(20) post_title text post_content longtext 重复记录例如: ID post_title post_content 44 无线网安全隐患解决之道 46 无线网安全隐患解决之道...121 无线网安全隐患解决之道
将重复的全部去掉: select a.* from tt a left join (select post_title from tt group by post_title having count(*)>=2) b on a.post_title=b.post_title where b.post_title is null
自己学会举一反三 delete a.* from tt2 a left join (select post_title from tt2 tt2group by post_title having count(*)>=2) b on a.post_title=b.post_title where b.post_title is not null
这样不行吗? delete from aa where post_title in (select post_title from aa group by post_title having count(*)>=2)
delete from yourTable a where exists (select id from yourTable where post_title=a.post_title and ID<a.ID);
in、exists效率低,连接是最快的方法
MySQL 中 delete从表中的语法不是 delete from table1 [xxx] join table2 on ..... 建议参考 MySQL手册。DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.*] [, tbl_name[.*] ...] USING table_references [WHERE where_definition]
字段 类型ID bigint(20)
post_title text
post_content longtext 重复记录例如:
ID post_title post_content
44 无线网安全隐患解决之道 46 无线网安全隐患解决之道...121 无线网安全隐患解决之道
select a.* from tt a left join
(select post_title from tt group by post_title having count(*)>=2) b
on a.post_title=b.post_title where b.post_title is null
delete a.* from tt2 a left join
(select post_title from tt2 tt2group by post_title having count(*)>=2) b
on a.post_title=b.post_title where b.post_title is not null
删除是下面一个SQL语句,在MYSQL5.1。37下测试没有问题,结果正确
delete from aa where post_title in (select post_title from aa group by post_title having count(*)>=2)
where exists (select id from yourTable where post_title=a.post_title and ID<a.ID);
建议参考 MySQL手册。DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.*] [, tbl_name[.*] ...]
USING table_references
[WHERE where_definition]