在mysql区发了帖,没人回,求一个sql语句,
就是我要删除某个人某类记录第十条以外的记录,
表结构简单表示为: id(主键,自增), int
type int
info string
atime datetime。
我试了delete from table where id = 1 and type = 1 order by atime desc limit 10,-1;
但mysql说语法错误。试了
delete from table where id not in (select id from table where type = 1 and id = 1 order by atime desc limit 10)
但mysql不支持limit在子查询中的应用。又试了
delete from table where id < (select min(id) as id from table where type = 7 and id = 1 order by time desc limit 10)
但mysql还不支持此语句。
因此请教大家,给个答案吧,头痛半天了。
就是我要删除某个人某类记录第十条以外的记录,
表结构简单表示为: id(主键,自增), int
type int
info string
atime datetime。
我试了delete from table where id = 1 and type = 1 order by atime desc limit 10,-1;
但mysql说语法错误。试了
delete from table where id not in (select id from table where type = 1 and id = 1 order by atime desc limit 10)
但mysql不支持limit在子查询中的应用。又试了
delete from table where id < (select min(id) as id from table where type = 7 and id = 1 order by time desc limit 10)
但mysql还不支持此语句。
因此请教大家,给个答案吧,头痛半天了。
解决方案 »
- Map keySet() entrySet() 最终结果不一致
- The Network Adapter could not establish the connection
- 这道题应该怎么分析并改正错误呢?
- 由BYTE[]转换为INT
- 求助this关键字的使用方法
- Jtable Jcheckbox 想做成前面一个checkbox,后面跟着一条记录,选中checkbox,也就选中该条,象sina邮箱,用Applet!
- 数学不好,请多指教!!!
- 睡觉前在线等个答案
- 这样的问题能难倒大家吗?不可能:::::::
- java中String不能分行写
- preparedStatement 问题2----很玄乎的问题--喜欢挑战的进来
- 静态域的同步化--有兴趣的进来讨论一下
你的头像很美...
平时一直在用mssql和Oracle
mssql中是这样
delete [table] where id not exists(select top(10) id from [table])
不过我想mysql中应该是。你可以试试
delete [from](mssql可以省去) [table] where id not in(select id from [table] limit 0,10)
我也是学子,多多指教!
delete from tableName where id not in(select top(10) id from tableName)
id是自增,select id from table where type = 1 and id = 1 order by atime desc limit 10
这句话就看不懂了,如果where中id = 1.那还要查id干什么?另外mysql不支持in(not in)的子查询中有limit,这种问题也解决不了?delete from table where id not in
(select id from table where 条件自己弄清楚 order by atime limit) ;不支持.
自己包一层就完全可以解决,太容易的问题.
delete from table where id not in
( select id from
(select id from table where 条件自己弄清楚 order by atime limit) b
) ;
OK,把里面的limit子查询再做一张虚表,对外层来说没有limit子查询了,骗外层一下就行了.
这样的语句还能拿到1000块钱,老板没少给.
delete from table where id not in (select id from my_view);
昏,我说了是简化表,语句 where id = 1中的id实际是一个人物id,和主键自增id不一样,
怪我在写时没标清。
说了你的语句不行,mysql不支持delete语句中查询部分和删除部分不能是一个表。
我发帖的最后一个语句已经做了。
自己没搞清就不要老批评别人。不实践就没有发言权!
(select id from table where 条件自己弄清楚 order by atime limit)
这种语句会报:
“you can't specify target table (table名) for update in from clause";
declare @num int;
begin
@num = select min(id) as id from table where type = 7 and id = 1 order by time desc limit 10;
if(@num >0 )
delete from table where id < @num;
commit;
end if;
end;
项目要求速度第一
delete from table where id not in
( select id from
(select id from table where 条件自己弄清楚 order by atime limit) b
) ;
绝对可是,除非你用的是火星版的mysql,因为是不是可以不是你说不可以就不可以的,其他人都可以验证.
id int(4) not null auto_increment primary key,
type int(4),
info varchar(20),
atime datetime
)插入测试记录
insert into test (type,info,atime) values (10,'test1',now());
insert into test (type,info,atime) values (10,'test2',now());
insert into test (type,info,atime) values (10,'test3',now());
insert into test (type,info,atime) values (10,'test4',now());
insert into test (type,info,atime) values (10,'test5',now());
insert into test (type,info,atime) values (10,'test6',now());
insert into test (type,info,atime) values (10,'test7',now());我们现在要保留type=10的五条记录,大于5条的删除. delete from test where id not in (select id from test where type = 10 order by atime limit 5);
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'换包装语句:delete from test where id not in (
select id from (
select id from test where type = 10 order by atime limit 5
) b
) and type = 10;Query OK, 2 rows affected (0.08 sec)成功删除了type = 10的多于5条记录的数据.mysql> select * from test where type = 10;
+----+------+-------+---------------------+
| id | type | info | atime |
+----+------+-------+---------------------+
| 15 | 10 | test1 | 2008-09-22 10:27:28 |
| 16 | 10 | test2 | 2008-09-22 10:27:28 |
| 17 | 10 | test3 | 2008-09-22 10:27:28 |
| 18 | 10 | test4 | 2008-09-22 10:27:28 |
| 19 | 10 | test5 | 2008-09-22 10:27:28 |
+----+------+-------+---------------------+
5 rows in set (0.00 sec)结果完全正确.
这个语句并不是说条件对不对,最主要的是在delete语句中直接order by还limit,你连删除和查询都没有弄清楚.
INNER JOIN (
SELECT a.id
FROM abc a
WHERE (
SELECT count(id)
FROM abc b
WHERE b.id <= a.id
AND b.type = '1' -- 查询条件
) > 10
AND a.type = '1' -- 查询条件
) tmp
ON abc.id = tmp.id;