在sqlite中删除+排序(Android) [英] Delete + order by in sqlite (Android)

问题描述

我有一个表格用于保存我的应用程序的排名，其中包含以下字段:[id,username,score]，我想清理表格，只保留前 100 个条目.

I have a table for saving the ranking of my app with the following fields: [id,username,score] and I want to clean the table keeping only the top 100 entries.

我该如何删除?我试过 DELETE FROM rating ORDER BY score DESC LIMIT 100,999999999) 但它返回一个错误:

How can I do this delete? I've tried DELETE FROM ranking ORDER BY score DESC LIMIT 100,999999999) but it returns an error:

Error: near "ORDER": syntax  error

我考虑过的其他选择是:

Other alternative I've considered is:

DELETE FROM ranking WHERE id NOT IN (SELECT id FROM ranking ORDER BY score 
  DESC LIMIT 100)

但我不知道它是否足够有效

but I dont know if it is efficient enought

推荐答案

我想你正在寻找这个:

DELETE FROM ranking WHERE id NOT IN (
    SELECT id FROM ranking ORDER BY score DESC LIMIT 100);

这里是 SQL Fiddle 说明了这个概念.

Here's SQL Fiddle illustrating the concept.

它非常有效(实际上，它非常典型)，因为嵌套查询仅执行一次.它实际上更多地取决于分数"是否被索引覆盖 - 与否:

It's quite efficient (in fact, it's quite typical), as the nested query is executed once only. It actually depends more on whether the 'score' is covered by index - or not:

(无索引):

EXPLAIN QUERY PLAN DELETE FROM ranking WHERE id NOT IN (
   SELECT id FROM ranking AS ranking_subquery ORDER BY score DESC LIMIT 2);

--
selectid   order   from   detail
0          0       0      SCAN TABLE ranking (~500000 rows)
0          0       0      EXECUTE LIST SUBQUERY 0
0          0       0      SCAN TABLE ranking AS ranking_subquery (~1000000 rows)
0          0       0      USE TEMP B-TREE FOR ORDER BY

(CREATE INDEX ts ON rating(score);之后)

selectid   order   from   detail
0          0       0      SCAN TABLE ranking (~500000 rows)
0          0       0      EXECUTE LIST SUBQUERY 0
0          0       0      SCAN TABLE ranking AS ranking_subquery USING INDEX ts (~1000000 rows)

这篇关于在sqlite中删除+排序(Android)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！