MYSQL查询语法问题,求助...

[kinglife2008]

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最小的..

求助

[kinglife2008]

补充下MYSQL版本和开发工具
MYSQL是5.0的 开发工具是 PHPMYADMIN

[liangCK]

DELETE A
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);

[ACMAIN_CHM]

没办法，MySQL不能在delete中再包含相同的表在子查询中。

换成这句就行了，相同的功能。

delete b1 from bbs_threads b1 ,bbs_threads b2 where b1.subject=b2.subject and b1.tid>b2.tid;

[Ginatom]

有选择的支持下

[cowge]

delete from bbs_threads
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
)

)

[kinglife2008]

用的虚拟主机

数据量很庞大 有几十万 执行上面朋友提供的语句出现假死

继续请求帮助

[ACMAIN_CHM]

在表上建立 subject 的索引 以提高查询速度。

[WWWWA]

重新生成一个新表如何？
贴你的记录及要求结果出来看看
假设只有两个字段
select subject,min(id) as newid from tt group by subject

[kinglife2008]

表的字段比较多 但是主要应用的就是 tid (唯一ID) 和 subject (标题) 这两个字段
表名 bbs_threads

表数据结构示意图

tid subject
1 标题一(重复)
2 标题二
3 标题三(重复标题)
4 标题一(重复)
5 标题五
6 标题三(重复标题)


现在我需要删除重复标题,只保留字段 tid 最小的值
也就是得到结果
tid subject
1 标题一(重复)
2 标题二
3 标题三(重复标题)
5 标题五



求执行效率高的SQL语句 多谢大家的帮忙!

[WWWWA]

哦
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.newid

OR
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

估计前一种要快一些