sqlite - 在 sqlite (Android) 中删除 + 排序依据

Question

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

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

Error: near "ORDER": syntax  error

我考虑过的其他选择是:

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

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

Answer 1

我想你正在寻找这个:

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

这是 SQL Fiddle说明这个概念。

它非常高效(事实上，它非常典型)，因为嵌套查询只执行一次。它实际上更多地取决于“分数”是否被索引覆盖:

(无索引):

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 ranking(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)