在MySQL中返回所有小数部分超过两位的数字

Question

我在我的数据库中有类似于这样的数据:

+----+----------------+
| id | summ (varchar) |
+----+----------------+
|  1 | 30.23          |
|  2 | 10.235         |
|  3 | 20.2300        |
|  4 | 100.005        |
|  5 | 5.031          |
|  6 | 0.0010002      |
+----+----------------+

如何在MySQL查询中获取所有具有两个或更多小数位（零也算）的数据？

起初，我只需要获取那些以5结尾且有3个数字的数据，所以我使用了这个命令substring( summs * 100, -2 ) = '.5'，但我不知道如何获取其他数字。

- Peon

---

您可以减去一个适当的“向下取整”的值。任何非零结果都是您想要的。 - Strawberry

Answer 1

您可以像这样做：-

SELECT * 
FROM `table` 
WHERE LENGTH(SUBSTR(`summ `,INSTR(`summ `,"."))) >3

- Rahul Tripathi

---

谢谢。我只是将>2改为>3，然后它就起作用了，因为>2也包括其查询中的**.**并返回2+而不是3+。 - Peon

Answer 2

你也可以像这样做：

SELECT *
FROM tableName
WHERE columnName <> ROUND (columnName, 2)

- rsmdh

Answer 3

所以你想让SQL查询排除"20.23"但包括"20.2300"，是吗？

考虑到这些都是字符串而不是浮点数，你可以在查询中使用字符串函数，特别是INSTR()和LENGTH()的组合。使用前者定位小数点（句点），然后将其与字符串长度（减2）进行比较。

- jarmod

Answer 4

我猜@Rahul Tripathi的解决方案很可能是最好的。

但是我在不同的上下文中尝试了一些东西，并根据这个问题的答案找到了另一个可能的解决方案：Store only numbers after Comma mysql

由于MySQL如何处理和存储浮点值等等，这个解决方案在正确工作的情况下受到了相当大的限制。

假设summ是一个数字（如果是字符串，则需要进行转换或者只需使用@Rahul Tripathi的解决方案）。而且summ最好是一个十进制数，而不是浮点数，因为它具有更高的精度和存储原因。

SELECT * 
FROM `table` 
WHERE (LENGTH(TRIM(TRAILING "0" from MOD(`summ`, 1))) - 2) > 2;

测试一下：

set @fraction = 100.005;
set @modulo = mod(@fraction, 1);
set @modulo = trim(trailing "0" from @modulo);
set @decimals = length(@modulo) - 2;

select @fraction, @modulo, @decimals;
+-----------+---------+-----------+
| @fraction | @modulo | @decimals |
+-----------+---------+-----------+
|   100.005 |   0.005 |         3 |
+-----------+---------+-----------+
1 row in set (0.00 sec)

注意：使用 CAST("12.345" as DECIMAL(10,5)) 进行强制类型转换需要先了解数字的位数限制。如果您在数据库中计算值或从字符串中进行转换，例如 "12.345" + 0，这种方法可能会有所作用，但由于二进制浮点数的存储方式，您将得到类似于 "12.345" + 0 → 12.34500000000000064 的结果，这可能不是您想要的答案。然后，您可以尝试再次四舍五入以删除某个位置后面的数字等等...

- E. Körner