以编程方式获取 SQL 中浮点的最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2699975/
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
Getting maximum value of float in SQL programmatically
提问by Axarydax
Is there an method for programmatically (in T-SQL)retrieving the maximum (and minimum) value of a datatype? That it would act like float.MaxValuein C#.
是否有以编程方式（在 T-SQL 中）检索数据类型的最大值（和最小值）的方法？它会像C# 中的float.MaxValue一样。
I would like to use it in some selection when the parameter does not equal any actual values in the database, so I would use something like
当参数不等于数据库中的任何实际值时，我想在某些选择中使用它，所以我会使用类似的东西
declare @min float
declare @max float
--fill @min and @max, can be null if undefined
select * from foo
where bar between isnull(@min,0 ) and isnull(@max,max(float)/*magic*/)
回答by SqlRyan
Though there doesn't appear to be any inline way to get the min or max values, there's a solution somebody put together:
虽然似乎没有任何内联方式来获得最小值或最大值，但有人提出了一个解决方案：
CREATE TABLE datatype_extrema
(min_bit bit NOT NULL DEFAULT (0) CHECK (min_Bit=0)
,max_bit AS CAST(0x1 AS bit)
,min_tinyint AS CAST(0x00 AS tinyint)
,max_tinyint AS CAST(0xFF AS tinyint)
,min_smallint AS CAST(0x8000 AS smallint)
,max_smallint AS CAST(0x7FFF AS smallint)
,min_int AS CAST(0x80000000 AS int)
,max_int AS CAST(0x7FFFFFFF AS int)
,min_bigint AS CAST(0x8000000000000000 AS bigint)
,max_bigint AS CAST(0x7FFFFFFFFFFFFFFF AS bigint)
,min_float AS CAST('-1.79E+308' AS float)
,max_float AS CAST('1.79E+308' AS float)
,min_real AS CAST('-3.40E+38' AS real)
,max_real AS CAST('3.40E+38' AS real)
,min_smalldatetime AS CAST('19000101 00:00' AS smalldatetime)
,max_smalldatetime AS CAST('20790606 23:59' AS smalldatetime)
,min_datetime AS CAST('17530101 00:00:00.000' AS datetime)
,max_datetime AS CAST('99991231 23:59:59.997' AS datetime)
)
INSERT INTO datatype_extrema DEFAULT VALUES
GO
CREATE TRIGGER nochange_datatype_extrema
ON datatype_extrema INSTEAD OF INSERT, UPDATE, DELETE
AS BEGIN
RAISERROR ('No changes allowed for table datatype_extrema.', 16, 1)
ROLLBACK TRANSACTION
END
GO
After that, you can either copy a maximum value to a local variable or (when using queries) cross join with this table.
之后，您可以将最大值复制到局部变量或（在使用查询时）与该表交叉连接。
Declare @max_int int
Set @max_int=(SELECT max_int FROM datatype_extrema)
IF COALESCE(@FirstInt, @max_int) < COALESCE(@SecondInt, 0)
回答by Oliver
Here are the defaults for the float and real type (that's missing in the accepted answer):
以下是 float 和 real 类型的默认值（已接受的答案中没有）：
select
CAST('-1.79E+308' AS float) as MinFloat,
CAST('1.79E+308' AS float) as MaxFloat,
CAST('-3.40E+38' AS real) as MinReal,
CAST('3.40E+38' AS real) as MaxReal
Unfortunately it is not possible to convert them from a varbinary, but varchar works without any problems.
不幸的是，不可能将它们从 varbinary 转换，但 varchar 可以正常工作。
回答by Anthony Faull
For floatand realthe minand maxvalues can be computed using the POWERfunction:
对于float和real的最小和最大值可以使用计算POWER功能：
SELECT
max_float = (1 + (POWER(2e0, 52) - 1) / POWER(2e0, 52)) * POWER(2e0, 1023)
, min_float = -(1 + (POWER(2e0, 52) - 1) / POWER(2e0, 52)) * POWER(2e0, 1023)
, max_real = CAST((1 + (POWER(2e0,23)-1)/POWER(2e0,23)) * POWER(2e0,127) AS real)
, min_real = CAST(-(1 + (POWER(2e0,23)-1)/POWER(2e0,23)) * POWER(2e0,127) AS real)
And these are the decimal values:
这些是十进制值：
SELECT
max_float = 1.7976931348623158E+308
, min_float = -1.7976931348623158E+308
, max_real = 3.4028234E+38
, min_real = -3.4028234E+38
回答by Kyle
1.79769313486231580799909999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999
This is the max number of floatvalue
这是浮点值的最大数量
Here's how you can get it:
获取方法如下：
DECLARE @decimal_length int = 0
DECLARE @decimal_value varchar(max) = '1.79'
DECLARE @decimal_value_buffer varchar(max) = @decimal_value
DECLARE @new_int varchar(10) = '9'
DECLARE @dummy float
WHILE @decimal_length < 308
BEGIN
SET @decimal_value = @decimal_value + @new_int
BEGIN TRY
SET @dummy = CAST(@decimal_value + 'E+308' AS float)
SET @decimal_length = @decimal_length + 1
SET @decimal_value_buffer = @decimal_value
SET @new_int = '9'
END TRY
BEGIN CATCH
SET @decimal_value = @decimal_value_buffer
SET @new_int = @new_int - 1
END CATCH
END
PRINT @decimal_value
