转自:http://blog.csdn.net/wangyuetingtao/article/details/8446474
在应用中,总要对用户的一些操作进行记录,比如播放历史记录,搜索历史记录等,方便用户再次使用。但是这些记录又不能无限的存储,只需要记录最新的50条或者100条就可以了,所以要求应用每插入一条新数据之后,对超过50条的数据进行清理。
建表
- CREATE
TABLE [search_history] ([keyword] CHAR UNIQUE,[time] TIMESTAMP NOT NULL ON CONFLICT REPLACE DEFAULT (datetime(‘now’)))
Sqlite之limit,offset关键词
1.一个参数 : limit x
其中x是一个整数,表示返回x条目记录。
2.两个参数: limit x , y
其中x , y 都是整数,表示跳过x 条记录,返回 y 条记录,即 LIMIT,
复合SQL语句演练:
- select
* from search_history where (select count(keyword) from search_history) >2
- select
keyword from search_history order by time desc limit (select count(keyword) from search_history) offset 2
- delete
from search_history where (select count(keyword) from search_history - )>
2 and keyword in (select keyword from search_history order by time desc limit (select count(keyword) from search_history) offset 2 )
- delete
from search_history where (select count(keyword) from search_history - )>
50 and keyword in (select keyword from search_history order by time desc limit (select count(keyword) from search_history) offset 50 ) - sqlite trigger 语法
-
// 保留最新的30条 -- 防止无限填充
NSString *deletesql = @"delete from DetailHistory where (select count(rowid) from DetailHistory)> 30 and rowid in (select rowid from DetailHistory order by rowid desc limit (select count(rowid) from DetailHistory) offset 30)";