以SQL方式获取float的最大值

发布时间：2020-12-12 16:16:54 所属栏目：MsSql教程来源：网络整理

导读：有没有一种方法来编程(在T-SQL中)检索数据类型的最大值(和最小值)？它将像C#中的float.MaxValue一样执行. 我想在某些选择中使用它,当参数不等于数据库中的任何实际值时,所以我会使用类似的东西 declare @min floatdeclare @max float--fill @min and @max,can

有没有一种方法来编程(在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*/)

谢谢

解决方法

虽然似乎没有任何内联方式获得最小或最大值,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_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)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!