以编程方式在SQL中获取float的最大值

小编典典

以编程方式在SQL中获取float的最大值

sql

是否有一种方法可以以编程方式 （在T-SQL中） 检索数据类型的最大值（和最小值）？就像C＃中的 float.MaxValue 一样。

当参数不等于数据库中的任何实际值时，我想在某些选择中使用它，因此我将使用类似

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*/)

阅读 192

2021-03-17

共1个答案

小编典典

尽管似乎没有任何内联方法来获取最小值或最大值，但是有人可以将其组合在一起：

 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

之后，您可以将最大值复制到局部变量，也可以（在使用查询时）与此表进行交叉联接。

  Declare @max_int int 
  Set @max_int=(SELECT max_int FROM datatype_extrema) 
  IF COALESCE(@FirstInt, @max_int) < COALESCE(@SecondInt, 0)

2021-03-17