mysql删除旧数据,保留最新的m条记录

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/u010002184/article/details/85717138

sql如下:

select   *  from  area_table ORDER BY id limit 1670,1  -- 结果id=1671 1条记录

select   *  from  area_table ORDER BY id limit 10 -- 结果id = 1...10 前10条记录

delete from area_table where id <
(select   id  from  area_table ORDER BY id desc limit 1670,1)
-- 错误
--  You can't specify target table 'area_table' for update in FROM clause

delete from area_table where id  not in (
select   id   from  area_table ORDER BY id desc limit 1000,1
) -- 错误
-- [Err] 1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

delete from area_table a ,(select   id   from  area_table ORDER BY id desc limit 1000,1) b
where a.id<b.id -- 错误

delete a from area_table a join (select   id   from  area_table ORDER BY id desc limit 901,1) b
on a.id<b.id  -- 正常

delete a from area_table a ,(select   id   from  area_table ORDER BY id desc limit 2000,1) b
where a.id<b.id -- 正常,没有2000条数据时不删除

delete a from area_table a ,(select  DISTINCT id   from  area_table ORDER BY id desc limit 900,1) b
where a.id<b.id -- 正常

版本:5.7.14

展开阅读全文

取第m条到n条记录带查询条件问题

12-10

asp+accessrnrn以下是代码段rn[code=VB]rn<%'上面是查询rnallcount=rsl.recordcountrnIF allcount mod 20 <>0 THENrnS=INT(ALL/20+1)rnELSErnS=ALL/20rnEND IF%>rn<%rnm=1rnn=20rnFOR i=1 TO S%>rn >rn rn 表头rn <%Set rs = Server.CreateObject ("ADODB.Recordset")rn if n=20 thenrn sqll="select top "&ss&" * from [u_mains] where"rn elsern sqll="select top "&ss&" * from [u_mains] where id not in (select top "&sss&" id from u_mains order by id asc) and"rn end ifrn rn if s_pc="1" then sqll=sqll&" u_pc='"&i_pc&"' and"rn if s_rq="1" then sqll=sqll&" u_date between #"&InTime1&"# and #"&InTime2&"# and"rn if s_cx="1" then sqll=sqll&" u_cx='"&i_cx&"' and"rn if s_name="1" then sqll=sqll&" u_name='"&i_name&"' and"rn if s_sex="1" then sqll=sqll&" u_sex='"&i_sex&"' and"rn if s_card="1" then sqll=sqll&" u_card='"&i_card&"' and"rn if s_tel="1" then sqll=sqll&" u_tel='"&i_tel&"' and"rn if s_address="1" then sqll=sqll&" u_address like '%"&i_address&"%' and"rnif right(sqll,5)="where" then sqll=left(sqll,clng(len(sqll))-5) ''''解决所有参数都为空的情况rnif right(sqll,3)="and" then sqll=left(sqll,clng(len(sqll))-3) ''''去掉参数最后的andrn sqll=sqll&" order by id asc"rn response.Write(""&sqll&"")rnrs.Open sqll,conn,1,1rnif rs.EOF and rs.BOF thenrnResponse.Write "暂时还没有内容"rnelsernpage_count=mrndo while not rs.eof%>rn 显示内容rn<%page_count=page_count+1rnrs.MoveNextrnLooprnend if%>rnrnrn<%N=N+20rnM=M+20%>rn<%NEXT%>rn[/code]rnrn这段如果加上条件查询的话,就不输出,请高手帮忙哦 论坛

没有更多推荐了,返回首页