TSQL-使文字浮点值 [英] TSQL - make a literal float value

问题描述

我了解比较浮点数的许多问题，并在这种情况下对它们的使用感到遗憾-但我不是表格创建者，攀登的障碍很小……

I understand the host of issues in comparing floats, and lament their use in this case - but I'm not the table author and have only a small hurdle to climb...

有人决定使用浮点数，因为您希望使用GUID.我需要检索具有特定浮点值的所有记录.

Someone has decided to use floats as you'd expect GUIDs to be used. I need to retrieve all the records with a specific float value.

sp_help MyTable

-- Column_name  Type    Computed    Length  Prec
-- RandomGrouping   float   no  8   53   

这是我天真的尝试:

--yields no results
SELECT RandomGrouping
FROM MyTable
WHERE RandomGrouping = 0.867153569942739

这是一个大概可行的尝试:

And here's an approximately working attempt:

--yields 2 records
SELECT RandomGrouping
FROM MyTable
WHERE RandomGrouping BETWEEN 0.867153569942739 - 0.00000001
      AND 0.867153569942739 + 0.00000001

--  0.867153569942739
--  0.867153569942739

在我幼稚的尝试中，该文字是浮点文字吗?还是真的是十进制文字会在以后转换?

In my naive attempt, is that literal a floating point literal? Or is it really a decimal literal that gets converted later?

如果我的文字不是浮点文字，那么制作浮点文字的语法是什么?

If my literal is not a floating point literal, what is the syntax for making a floating point literal?

我发生了另一种可能性...可能是此列中存储的数字比显示的数字更精确.创建表示该数字的文字可能是不可能的.我将接受证明事实确实如此的答案.

Another possibility has occurred to me... it may be that a more precise number than is displayed is stored in this column. It may be impossible to create a literal that represents this number. I will accept answers that demonstrate that this is the case.

对DVK的响应.

TSQL是MSSQLServer的SQL方言.

TSQL is MSSQLServer's dialect of SQL.

此脚本有效，因此可以确定性地在float类型之间执行相等操作:

This script works, and so equality can be performed deterministically between float types:

DECLARE @X float
SELECT top 1 @X = RandomGrouping
FROM MyTable
WHERE RandomGrouping BETWEEN 0.839110948199148 - 0.000000000001
  AND 0.839110948199148 + 0.000000000001
  --yields two records
SELECT *
FROM MyTable
WHERE RandomGrouping = @X

我说大约"是因为该方法测试一个范围.通过这种方法，我可以获得的值不等于我的预期值.

I said "approximately" because that method tests for a range. With that method I could get values that are not equal to my intended value.

链接的文章不适用，因为我不是(故意)试图跨越小数和浮点数之间的世界边界.我正在尝试仅使用浮点数.这与小数转换为浮点数无关.

The linked article doesn't apply because I'm not (intentionally) trying to straddle the world boundaries between decimal and float. I'm trying to work with only floats. This isn't about the non-convertibility of decimals to floats.

回复Zinglon:

一个可以找到我的记录的文字值，谢谢.

A literal value that can find my records, thanks.

DECLARE @Y binary(8)
SET @Y = 0x3FEAD9FF34076378

SELECT *
FROM MyTable
WHERE convert(binary(8), RandomGrouping) = @Y

推荐答案

显示的值可能会被截断.我假设该列没有唯一的约束，否则问题就没有意义了.在我的设置中，SSMS会截断此脚本中更精确的值.

It is possible that the values are being truncated on display. I'm assuming the column doesn't have a unique constraint on it, otherwise the question would be moot. On my setup, SSMS truncates the more precise value in this script.

create table flt ( f float not null primary key )
insert into flt
select 0.111111111111111
union all
select 0.1111111111111111
select f, cast(f as binary(8)) from flt

类似地，如果这些值是不同的，则可以将它们转换为binary(8)并根据该值进行标识，如下所示:

Similarly, if these values are distinct you can cast them to binary(8) and identify them based on that value, like this:

select f from flt
where cast(f as binary(8)) = 0x3FBC71C71C71C71C

这篇关于TSQL-使文字浮点值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！