SQL 选择小数点后 4 位以上的数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32462644/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select numbers with more than 4 decimal places
提问by BernieSF
I have a SQL table with one float column populated with values like these:
我有一个 SQL 表，其中一个浮点列填充了如下值：
1.4313
3.35
2.55467
6.22456
3.325
I need to select rows containing only values with more than 4 decimals. In this case, the select must return:
我需要选择仅包含超过 4 位小数的值的行。在这种情况下，选择必须返回：
2.55467
6.22456
Ideas? Thanks!
想法？谢谢！
This is what I have tried so far
这是我迄今为止尝试过的
select *
from table
where CAST(LATITUDE AS DECIMAL(10,5)) - LATITUDE = 0
回答by UnhandledExcepSean
DECLARE @tbl TABLE (val float)
INSERT INTO @tbl SELECT 1234.567
INSERT INTO @tbl SELECT 1234.5678
INSERT INTO @tbl SELECT -1234.5678
INSERT INTO @tbl SELECT 1234.56789
SELECT *
from @tbl
where (((abs(val)*10000) - CONVERT(INT,(abs(val)*10000))) <> 0)
回答by Alif Noushad
Why cant we make it simple by this query:-
为什么我们不能通过这个查询使它简单：-
SELECT * FROM table WHERE val LIKE '%.____%'
This selects what we want
这选择了我们想要的
回答by torno
回答by xQbert
http://sqlfiddle.com/#!3/abadc/3/0
http://sqlfiddle.com/#!3/abadc/3/0
Seems like something like this should work...
似乎这样的事情应该工作......
all it does is convert the number to an integer to drop off decimals after multiplying it * 10 to power of decimals you need then it compares that int version of the number to the base number after it too was multiplied by 10 to the power of # of decimals.
它所做的只是将数字转换为整数以在将其乘以 * 10 为您需要的小数次幂后去掉小数，然后将数字的 int 版本与基数进行比较，然后将其乘以 10 的幂 #的小数。
If the numbers don't match, then you have decimals beyond 4. If they do match, then it was 4 or fewer.
如果数字不匹配，则小数点超过 4。如果它们匹配，则为 4 或更少。
Select *
from foo
where cast(myNum*power(10,4) as int) <> myNum*power(10,4)
回答by agondaku
SELECT *
FROM table WHERE
(abs(val)*100000)%10 <> 0
回答by Lukas
Another solution also:
另一种解决方案也是：
SELECT * from table
where (round(value,2) - round(value,4) <> 0)
回答by Roman Marusyk
Please try something like:
请尝试以下操作：
select * from table
where RIGHT(CAST(value as DECIMAL(10,5)), value), 1) != 0
回答by BrunoFacca
This works on Postgres 11:
这适用于 Postgres 11：
SELECT * FROM mytable WHERE mycolumn != ROUND(mycolumn::numeric,2)
回答by rsa
- Cast the number as text
- Split the text using '.' as separator
- Use the 2nd index and apply a length
- Filter
- 将数字投射为文本
- 使用 '.' 拆分文本 作为分隔符
- 使用第二个索引并应用长度
- 筛选
--i.e. with postgreSQL.
--1)
select data_numeric, length(arr[2]) as str_length
from (
select data_numeric, regexp_split_to_array(data_numeric::text, '\.') as arr from TABLE
) temp;
--2)
with t1 as (
select data_numeric, regexp_split_to_array(data_numeric::text, '\.') as arr from TABLE
), t2 as (
select data_numeric, arr[2] as decimals, length(arr[2]) as length from t1
)
select * from t2;
回答by AdemusPrime
It's an older question but it checks out.
这是一个较旧的问题，但它会检查出来。
select val
from table
where ((val * 100) % 1) > 0
Change 100 to your precision.
将 100 更改为您的精度。
