delete 语句在使用in的时候不能在自身在做查询,因此用in(where ...)查询的时候不能是自身表,必须分开查询,按照你的思路,你可以先把所有符合条件的id预先查询出来, select id from piao_post a where (a.title,a.content) in (select title,content from piao_post group bytitle,content having count(*) > 1) and pid not in (select min(pid) from piao_post group by title,content havingcount(*)>1) 然后再做删除。
where (a.title,a.content) 跟了2个字段,应该只能有一个字段后面再跟IN吧! where a.title in (select title from piao_post group by title having count(*) > 1)
首先告诉各位小爷,mysql是支持2个字段后面再跟IN的(什么不信,自己做个测试吧),其次楼主出错的原因 应该是报错:1093 - You can't specify target table content for update in FROM clause 也就是说不能根据select一个表得到的条件,来同时delete(其实update也不行)这个表。用in(表)查询是没问题了(楼主查询估计也没有问题),不过更新与删除就不行了!解决的方法就是建立一个临时表,然后用临时表去删了 create table tmp as (select * from piao_post a where (a.title,a.content) in (select title,content from piao_post group by title,content having count(*) > 1) and pid not in (select min(pid) from piao_post group by title,content having count(*)>1))//先建立个表tep delete from `piao_post` where pid in(select pid from `tmp`)//然后删除自己想要的drop table tmp;//最后删除表tep本人只为分数来
这个问题关键是select子句不支持分组,也就是子句中不能有group by...你的那个查询语句虽然没报错,但得到的一定是个空记录集。那个删除语句就不行了,对一个空记录集进行操作,怎能不报错呢。临时表 或者 视图 是个不错的选择。以临时表为例 DROP table IF EXISTS tmp ;#这一句可有可无,因为临时表在执行后系统会自动删除的。 create temporary table tmp as select * from piao_post group by title,content ; delete from piao_post where id not in (select id from tmp) 删除语句中的ID,应改成表piao_post中的主键
select id from piao_post a
where (a.title,a.content) in (select title,content from piao_post group bytitle,content having count(*) > 1)
and pid not in (select min(pid) from piao_post group by title,content havingcount(*)>1)
然后再做删除。
where a.title in (select title from piao_post group by title having count(*) > 1)
应该是报错:1093 - You can't specify target table content for update in FROM clause
也就是说不能根据select一个表得到的条件,来同时delete(其实update也不行)这个表。用in(表)查询是没问题了(楼主查询估计也没有问题),不过更新与删除就不行了!解决的方法就是建立一个临时表,然后用临时表去删了
create table tmp as (select * from piao_post a where (a.title,a.content) in (select title,content from piao_post group by title,content having count(*) > 1) and pid not in (select min(pid) from piao_post group by title,content having count(*)>1))//先建立个表tep
delete from `piao_post` where pid in(select pid from `tmp`)//然后删除自己想要的drop table tmp;//最后删除表tep本人只为分数来
DROP table IF EXISTS tmp ;#这一句可有可无,因为临时表在执行后系统会自动删除的。
create temporary table tmp as
select * from piao_post group by title,content ;
delete from piao_post
where id not in (select id from tmp)
删除语句中的ID,应改成表piao_post中的主键