delete a from a where id not ni (select id from tt order by id desc limit 1000)
还是没明白,比如我现在有一张表a,如下: id(自增主键) | flag | value 1 | 0 | 12 2 | 1 | 17 3 | 1 | 2 4 | 0 | 9 5 | 1 | 8 6 | 1 | 5 7 | 1 | 11 我要保持flag =1的记录只有3条,首先我select count(*) from a where flag=1,结果为5>3 下面我要调用函数删除,最早的flag =1的记录 如果用最笨的方法就是判断count是否大于3,如果大于,循环下面的语句: DELETE FORM a where id=(SELECT id FROM a WHERE flag=1 ORDER BY id ASC LIMIT 1) 不过这个方法太笨了,所以问下有什么简单点的方法或思路 你写的SQL语句我还是没看明白啊,delete a from a 是什么意思,没遇到过这种写法啊
看看MYSQL的HELP。基本语法delete a from a left join (select id from tt order by id desc limit 3) b on a.id=b.id where b.id is null
你这个语句好像把最新的3条都删除了啊,早的反而没被删除掉 是不是这样写的 DELETE a FROM test_tb AS a LEFT JOIN (SELECT id FROM test_tb WHERE flag=1 ORDER BY id DESC LIMIT 3) b ON a.id=b.id WHERE flag=1
你这个语句好像把最新的3条都删除了啊:不会吧,有条件 where b.id is nulldelete a from a left join (select id from tt order by id desc limit 3) b on a.id=b.id where b.id is null
DELETE a FROM test_tb AS a LEFT JOIN (SELECT id FROM test_tb WHERE flag=1 ORDER BY id DESC LIMIT 3) b ON a.id=b.id WHERE flag=1 AND b.id is null 对了,谢谢
delete a from a left join
(select id from tt order by id desc limit 1000) b
on a.id=b.id where b.is is null
delete a from a left join
(select id from tt order by id desc limit 1000) b
on a.id=b.id where b.id is null
问题很简单,就是自动更新,有新的进来,就把最早的删除,只不过多加了个flag=1的条件啊
另征集定时执行mysql数据库任务方案。
id(自增主键) | flag | value
1 | 0 | 12
2 | 1 | 17
3 | 1 | 2
4 | 0 | 9
5 | 1 | 8
6 | 1 | 5
7 | 1 | 11
我要保持flag =1的记录只有3条,首先我select count(*) from a where flag=1,结果为5>3
下面我要调用函数删除,最早的flag =1的记录
如果用最笨的方法就是判断count是否大于3,如果大于,循环下面的语句:
DELETE FORM a where id=(SELECT id FROM a WHERE flag=1 ORDER BY id ASC LIMIT 1)
不过这个方法太笨了,所以问下有什么简单点的方法或思路
你写的SQL语句我还是没看明白啊,delete a from a 是什么意思,没遇到过这种写法啊
(select id from tt order by id desc limit 3) b
on a.id=b.id where b.id is null
是不是这样写的
DELETE a FROM test_tb AS a LEFT JOIN (SELECT id FROM test_tb WHERE flag=1 ORDER BY id DESC LIMIT 3) b ON a.id=b.id WHERE flag=1
where b.id is nulldelete a from a left join
(select id from tt order by id desc limit 3) b
on a.id=b.id where b.id is null
对了,谢谢