sql - SQLite3查询中ORDER BY和LIMIT的异常行为

我正在尝试返回sqlite3数据库中funny字段中前10位值的记录。我使用以下查询：

SELECT * FROM ngrams
ORDER BY funny DESC
LIMIT 10;

这将返回10个记录，该记录按funny字段降序排列。但是，结果似乎只是滑稽的

SELECT Count(*) FROM ngrams
WHERE funny > 10;

如何返回前十个funny值的记录？

列名称为ngram，funny，useful，cool，ngcount。

输出：

sqlite> SELECT * FROM ngrams ORDER BY funny DESC LIMIT 10;
jetta| 9.914530| 5.726496| 6.367521| 117
emblem| 9.900000| 3.800000| 4.300000| 10
bt's| 9.875000| 11.375000| 10.625000| 8
dear phoenix| 9.857143| 8.285714| 7.857143| 7
look alikes| 9.857143| 7.428571| 6.428571| 7
alikes| 9.857143| 7.428571| 6.428571| 7
years im| 9.833333| 9.500000| 10.000000| 6
waaa| 9.833333| 8.000000| 7.000000| 6
earth do| 9.833333| 5.833333| 6.166667| 6
still full i| 9.833333| 7.500000| 8.000000| 6
sqlite> SELECT Count(*) FROM ngrams WHERE funny > 10;
3718
sqlite> SELECT * FROM ngrams WHERE ngram = 'megaphone';
megaphone| 14.777778| 10.555556| 10.555556| 9
sqlite> SELECT * FROM (
   ...> SELECT * FROM ngrams
   ...> ORDER BY funny DESC
   ...> ) topfunny LIMIT 10;
jetta| 9.914530| 5.726496| 6.367521| 117
emblem| 9.900000| 3.800000| 4.300000| 10
bt's| 9.875000| 11.375000| 10.625000| 8
dear phoenix| 9.857143| 8.285714| 7.857143| 7
look alikes| 9.857143| 7.428571| 6.428571| 7
alikes| 9.857143| 7.428571| 6.428571| 7
years im| 9.833333| 9.500000| 10.000000| 6
waaa| 9.833333| 8.000000| 7.000000| 6
earth do| 9.833333| 5.833333| 6.166667| 6
still full i| 9.833333| 7.500000| 8.000000| 6
sqlite>

最佳答案

您的funny列是TEXT。鉴于这种：

create table ngrams (ngram text, funny text);
insert into ngrams (ngram, funny) values ('jetta', '9.914530');
insert into ngrams (ngram, funny) values ('emblem', '9.900000');
insert into ngrams (ngram, funny) values ('bt''s', '9.875000');
insert into ngrams (ngram, funny) values ('dear phoenix', '9.857143');
insert into ngrams (ngram, funny) values ('look alikes', '9.857143');
insert into ngrams (ngram, funny) values ('alikes', '9.857143');
insert into ngrams (ngram, funny) values ('years im', '9.833333');
insert into ngrams (ngram, funny) values ('waaa', '9.833333');
insert into ngrams (ngram, funny) values ('earth do', '9.833333');
insert into ngrams (ngram, funny) values ('still full i', '9.833333');
insert into ngrams (ngram, funny) values ('megaphone', '14.777778');

我得到以下输出（出于可读性考虑）：

sqlite> select * from ngrams order by funny desc;
ngram       |funny
jetta       |9.914530
emblem      |9.900000
bt's        |9.875000
dear phoenix|9.857143
look alikes |9.857143
alikes      |9.857143
years im    |9.833333
waaa        |9.833333
earth do    |9.833333
still full i|9.833333
megaphone   |14.777778

但是，如果funny值为数字：

create table ngrams (ngram text, funny real);
insert into ngrams (ngram, funny) values ('jetta', 9.914530);
insert into ngrams (ngram, funny) values ('emblem', 9.900000);
insert into ngrams (ngram, funny) values ('bt''s', 9.875000);
insert into ngrams (ngram, funny) values ('dear phoenix', 9.857143);
insert into ngrams (ngram, funny) values ('look alikes', 9.857143);
insert into ngrams (ngram, funny) values ('alikes', 9.857143);
insert into ngrams (ngram, funny) values ('years im', 9.833333);
insert into ngrams (ngram, funny) values ('waaa', 9.833333);
insert into ngrams (ngram, funny) values ('earth do', 9.833333);
insert into ngrams (ngram, funny) values ('still full i', 9.833333);
insert into ngrams (ngram, funny) values ('megaphone', 14.777778);

然后输出就是您期望的：

sqlite> select * from ngrams order by funny desc;
ngram       |funny
megaphone   |14.777778
jetta       |9.91453
emblem      |9.9
bt's        |9.875
dear phoenix|9.857143
look alikes |9.857143
alikes      |9.857143
years im    |9.833333
waaa        |9.833333
earth do    |9.833333
still full i|9.833333

SQLite的松散类型系统既是福也是祸，您必须非常小心以确保您和SQLite在某种东西上是什么类型（在模式和您使用的值中）。如果SQLite没有固定的精度numeric或decimal类型，则输出中的尾随零将完全消失，这说明存在某些问题。