delete from bbs_threads
where subject in (select subject from bbs_threads group by subject having count(subject)>1)
and tid not in (select min(tid) from bbs_threads group by subject having count(subject)>1)字段 TID 就是 bbs_threads (主题表) 的主题ID
字段 subject 就是 主题标题我的目的是删除重复标题的帖子,只保留ID最小的..求助
where subject in (select subject from bbs_threads group by subject having count(subject)>1)
and tid not in (select min(tid) from bbs_threads group by subject having count(subject)>1)字段 TID 就是 bbs_threads (主题表) 的主题ID
字段 subject 就是 主题标题我的目的是删除重复标题的帖子,只保留ID最小的..求助
MYSQL是5.0的 开发工具是 PHPMYADMIN
FROM bbs_threads AS A
JOIN (
SELECT MIN(id) AS id,subject
FROM bbs_threads
GROUP BY subject
) AS B
ON A.subject=B.subject AND A.id>B.id;--看看支不支持这种写法DELETE A
FROM bbs_threads AS A
WHERE NOT EXISTS(SELECT * FROM bbs_threads
WHERE A.subject=subject
AND id<A.id);
delete b1 from bbs_threads b1 ,bbs_threads b2 where b1.subject=b2.subject and b1.tid>b2.tid;
贴你的记录及要求结果出来看看
假设只有两个字段
select subject,min(id) as newid from tt group by subject
表名 bbs_threads表数据结构示意图tid subject
1 标题一(重复)
2 标题二
3 标题三(重复标题)
4 标题一(重复)
5 标题五
6 标题三(重复标题)
现在我需要删除重复标题,只保留字段 tid 最小的值
也就是得到结果
tid subject
1 标题一(重复)
2 标题二
3 标题三(重复标题)
5 标题五求执行效率高的SQL语句 多谢大家的帮忙!
create table newtt as
select a.* from a
inner join
(select subject,min(tid) as newid from tt group by subject) b
on a.subject=b.subject and a.tid=b.newidOR
DELETE A FROM TT A INNER JOIN
(select subject,min(tid) as newid from tt group by subject) b
on a.subject=b.subject and a.tid=b.newid估计前一种要快一些
where tid in
(
select a.tid from bbs_threads as a
where a.tid>(
select min(b.tid) from bbs_threads as b where a.tid=b.tid
))