手机浏览 RSS 2.0 订阅 膘叔的简单人生 , 腾讯云RDS购买 | 超便宜的Vultr , 注册 | 登陆
浏览模式: 标准 | 列表2011年10月15日的文章

mysql子查询删除

Submitted by gouki on 2011, October 15, 6:06 PM

这是我试了很多的代码之后才。。。。
#select id,count(1) as cnt from feeds_datas group by link HAVING count(1) > 7 order by cnt DESC
#delete from feeds_datas where link = (select link from feeds_datas WHERE id in ()
#delete from feeds_datas where id in (6697,7127,6798,4557,4558,6086,6087,6088,6089)
#select id from feeds_datas group by link HAVING count(link) > 1

#delete from feeds_datas as a WHERE a.in in (select b.id from feeds_datas as b group by b.link HAVING count(b.link) > 1 )
#delete from feeds_datas t1,(select link from feeds_datas group by link HAVING count(1) > 1) t2 where t1.link = t2.link

#and id not in (select min(id) from  feeds_datas  group by link  having count(link )>1)

delete feeds_datas as a from feeds_datas as a,
(
select *,min(id) from feeds_datas group by link having count(1) > 1
) as b
 where a.link = b.link and a.id > b.id;

加#的都是失败的，其中最后一条SQL我很纳闷，如果把delete 后面的 feeds_datas as a去掉，那么sql就报错，说是不许right syntax之类的。加上就OK。
记录一下。。
网上有很多资料，可以参考：
1、http://yueliangdao0608.blog.51cto.com/397025/81390
2、http://zhidao.baidu.com/question/85817899

Tags: mysql, 子查询, 删除

DataBase | 评论:1 | 阅读:18913