带有小数/浮点比较的 sqlite select 查询不起作用

Question

我需要使用简单的 select 来执行十进制比较操作，例如：

select * from table_a where time > 0.0004

ID 时间

---

0 0.000502
1 0.000745
2 0.000725
4 0.000197
5 0.000176
6 0.000833
7 0.000269
8 0.000307
9 0.000302

结果包含满足比较的值。简而言之，“time > 0.0004”始终评估为 true，“time < 0.0004”始终评估为 false。

我尝试将时间列的类型从十进制转换为浮点数，但没有成功。

如果我使用子查询，它会正确执行查询：

select * from table_a where time > （从 table_a 中选择时间，其中 id=8）；

ID 时间

---

0 0.000502
1 0.000745
2 0.000725
6 0.000833

表创建：

CREATE TABLE "table_a" ("id" 整数 NOT NULL PRIMARY KEY, "time" 小数 NOT NULL);

Answer 1

感谢您的所有建议，我找到了问题的根源：

“时间”列值被作为“文本”处理。为什么？

我使用“.import”将数据导入到表中，而我的 CSV 文件在小数值周围包含空格。不知何故，即使类型不匹配，SQLite 的导入命令也允许插入发生。从 CSV 文件中删除空格后，数据将作为“真实”插入，这样可以正确进行数字比较。但是，它没有解释为什么“十进制”列中的数据是“实数”类型，而不是“十进制”或“数字”，如其文档中所述： http://www.sqlite.org/datatype3.html

select id, time, typeof(time) from table_a;
id    time           type
----  -------------  ----
0      0.000502      text
1      0.000745      text
2      0.000725      text
4      0.000197      text
5      0.000176      text
6      0.000833      text
7      0.000269      text
8      0.000307      text
9      0.000302      text

select id, time, typeof(time) from table_b;
id    time           type
----  -------------  ----
0     0.000502       real
1     0.000745       real
2     0.000725       real
4     0.000197       real
5     0.000176       real
6     0.000833       real
7     0.000269       real
8     0.000307       real
9     0.000302       real

Answer 2

SQLite 有一个非正统的动态类型系统，其中列的类型并不决定列中实际数据的类型，它只是鼓励 SQLite 将数据转换为特定类型（如果可能的话）。

当尝试比较字符串和数字时，如果字符串不是格式正确的数字，则它被认为大于数字。

因此，一个明显的猜测是，由于某种原因，这些时间值实际上不是数字，而是字符串。这令人费解有两个原因。 (1) 由于 time 列的类型为 decimal，因此它应该具有“数字”亲和性，这应该将存储在其中的任何看起来像数字的内容转换为数字。 (2) 即使值存储为字符串，它们仍应转换为数字以与 0.0004 进行比较。

为什么他们没有归信呢？可能性#1：也许它们包含额外的空格或类似的东西。可能性#2：也许您的语言环境想要使用 . 之外的其他内容作为小数点。 （很可能还有其他我没有想到的可能性。）

如果您在表中插入一条实际上包含数字的记录 - insert into table_a (id,time)values (999,0.0001) 或类似的东西 - 该记录是否包含在您的选择中？

Answer 3

老问题，但我想给出另一个答案。

因为 SQLite 倾向于将所有内容视为文本，所以会发生以下情况：

如果您查询 ... 其中 a < a 被视为文本，它将永远找不到任何内容

1000如果您查询 ... 其中 a 如果 '1000' 它将把千作为某种文本进行比较，如果数字具有不同的字符长度，则会出现问题。 1000 是 4 个字符，100 是 3 个字符，等等

但是有 CAST 表达式 http://www.sqlite。 org/lang_expr.html#castexpr

所以... 其中cast(a as Integer) < 1000 将得到你想要的结果（ofc可以用REAL来完成浮点数）

Answer 4

我无法重现您所看到的行为。即使我

• 将时间列定义为 varchar、
• 在单引号内插入值、
• 在双引号内插入值、
• 将时间列定义为小数， 我也会得到正确的结果
  首先插入文本文字（“测试文本”）

但是，像 Samuel Neff 一样，我仍然希望在数据库中查看 SELECT id, time, typeof(time) FROM table_a; 的结果。你知道还有什么可能有趣吗？ 这些结果

select * from table_a order by time;

来自版本 3.7.4，可能不是当前版本。

sqlite> .dump table_a
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE "table_a" ("id" integer NOT NULL PRIMARY KEY, "time" decimal NOT NULL);
INSERT INTO "table_a" VALUES(1,0.4);
INSERT INTO "table_a" VALUES(2,0.6);
INSERT INTO "table_a" VALUES(3,0.0005);
INSERT INTO "table_a" VALUES(4,0.0006);
INSERT INTO "table_a" VALUES(5,0.0004);
COMMIT;

sqlite> select * from table_a where time < 0.0005;
5|0.0004

sqlite> SELECT id, time, typeof(time) FROM table_a;
1|0.4|real
2|0.6|real
3|0.0005|real
4|0.0006|real
5|0.0004|real

Answer 5

显然这不是你的情况，但请记住，有时一些管理工具（例如 SQLite Administrator 或 < a href="http://www.sqliteexpert.com/" rel="nofollow noreferrer">SQLite Expert）可能会向您显示实际值相等，但事实并非如此。这是一个示例：

差异很小，但可能足以让您头疼。