SQL表达式结果集的小数精度

最新推荐文章于 2021-11-17 17:25:45 发布

htl258_Tony

最新推荐文章于 2021-11-17 17:25:45 发布

阅读量3k

点赞数

分类专栏：数据库设计基础应用原创与心得文章标签： sql sql server microsoft 存储 service windows

本文链接：https://blog.youkuaiyun.com/htl258/article/details/5715979

版权

原创与心得同时被 3 个专栏收录

96 篇文章

订阅专栏

基础应用

42 篇文章

订阅专栏

数据库设计

2 篇文章

订阅专栏

精度、小数位数和长度 (Transact-SQL)

精度是数字中的数字个数。小数位数是数中小数点右边的数字个数。例如，数 123.45 的精度是 5，小数位数是 2。

在 SQL Server 中，numeric 和 decimal 数据类型的默认最大精度为 38。在 SQL Server 早期版本中，默认最大精度为 28。

数字数据类型的长度是存储此数所占用的字节数。字符串或 Unicode 数据类型的长度是字符个数。binary、varbinary 和 image 数据类型的长度是字节数。例如，int 数据类型可以有 10 位数，用 4 个字节存储，不接受小数点。int 数据类型的精度是 10，长度是 4，小数位数是 0。

当两个 char、varchar、binary 或 varbinary 表达式串联时，所生成表达式的长度是两个源表达式长度之和，或是 8,000 字符，以二者中少者计。

当两个 nchar 或 nvarchar 表达式串联时，所生成表达式的长度是两个源表达式长度之和，或是 4,000 字符，以二者中少者计。

使用 UNION、EXCEPT 或 INTERSECT 对数据类型相同但长度不同的两个表达式进行比较时，得到的长度为两个表达式中较大的长度。

除了 decimal 类型之外，数字数据类型的精度和小数位数是固定的。如果算术运算符有两个相同类型的表达式，结果就为该数据类型，并且具有对此类型定义的精度和小数位数。如果运算符有两个不同数字数据类型的表达式，则由数据类型优先级决定结果的数据类型。结果具有为该数据类型定义的精度和小数位数。

下表定义了当运算结果是 decimal 类型时，如何计算结果的精度和小数位数。当下列任一条件成立时，结果为 decimal：

两个表达式都是 decimal 类型。
一个表达式是 decimal 类型，而另一个是比 decimal 优先级低的数据类型。

操作数表达式由表达式 e1（精度为 p1，小数位数为 s1）和表达式 e2（精度为 p2，小数位数为 s2）来表示。非 decimal 类型的任何表达式的精度和小数位数，是对此表达式数据类型定义的精度和小数位数。

运算	结果精度	结果小数位数 *
e1 + e2	max(s1, s2) + max(p1-s1, p2-s2) + 1	max(s1, s2)
e1 - e2	max(s1, s2) + max(p1-s1, p2-s2) + 1	max(s1, s2)
e1 * e2	p1 + p2 + 1	s1 + s2
e1 / e2	p1 - s1 + s2 + max(6, s1 + p2 + 1)	max(6, s1 + p2 + 1)
e1 { UNION \| EXCEPT \| INTERSECT } e2	max(s1, s2) + max(p1-s1, p2-s2)	max(s1, s2)
e1 % e2	min(p1-s1, p2 -s2) + max( s1,s2 )	max(s1, s2)

* 结果精度和小数位数的绝对最大值为 38。当结果精度大于 38 时，相应的小数位数会减少，以避免结果的整数部分被截断。

----------------------------------------------------------------------------------

-- Author : htl258(Tony)

-- Date : 2010-07-06 19:33:50

-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

-- Jul 9 2008 14:43:34

-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

-- Blog : http://blog.youkuaiyun.com/htl258

-- Subject: SQL表达式结果集的小数精度问题整理

----------------------------------------------------------------------------------

--创建演示环境：

PRINT 'e1(p1,s1) e2(p2,s2) ' --公式中的各参数代码

PRINT 'p1=20'+CHAR(10)+'p2=25'+CHAR(10)+'s1=10'+CHAR(10)+'s2=12'; --显示各参数的值

DECLARE @e1 DECIMAL(20,10),@e2 DECIMAL(25,12) --声明表达式用到的变量

SELECT @e1=10,@e2=100; --为变量赋值

--1.加法

--示例：

SELECT @e1+@e2 AS 结果,LEN(@e1+@e2)-CHARINDEX('.',@e1+@e2) AS 结果小数位数

结果结果小数位数

--------------------------------------- -----------

110.000000000000 12

(1 行受影响)

--说明：

--结果精度：max(s1, s2) + max(p1-s1, p2-s2) + 1 , 结果小数位数：max(s1, s2) --公式

--结果精度：max(10, 12) + max(20-10, 25-12) + 1 , 结果小数位数：max(10, 12) --替代值

SELECT 12+(25-12) AS 结果精度,12 AS 结果小数位数

结果精度结果小数位数

----------- -----------

25 12

(1 行受影响)

--2.减法

--示例：

SELECT @e1-@e2 AS 结果,LEN(@e1+@e2)-CHARINDEX('.',@e1-@e2) AS 结果小数位数

结果结果小数位数

--------------------------------------- -----------

-90.000000000000 12

(1 行受影响)

--说明：

--结果精度：max(s1, s2) + max(p1-s1, p2-s2) + 1 , 结果小数位数：max(s1, s2) --公式

--结果精度：max(10, 12) + max(20-10, 25-12) + 1 , 结果小数位数：max(10, 12) --替代值

SELECT 12+(25-12) AS 结果精度,12 AS 结果小数位数

结果精度结果小数位数

----------- -----------

25 12

(1 行受影响)

--3.乘法

--示例：

SELECT @e1*@e2 AS 结果,LEN(@e1*@e2)-CHARINDEX('.',@e1*@e2) AS 结果小数位数

结果结果小数位数

--------------------------------------- -----------

1000.00000000000000 14

(1 行受影响)

--说明：

--结果精度：p1 + p2 + 1, 结果小数位数：s1 + s2 ： --公式

--结果精度：+ 25 + 1, 结果小数位数：+ 12 ，以下是伪值： --替代值

SELECT 20+25+1 AS 结果精度,10+12 AS 结果小数位数

结果精度结果小数位数

----------- -----------

46 22

(1 行受影响)

--由于结果精度和小数位数的绝对最大值为38。

--当结果精度大于38 时，相应的小数位数会减少，以避免结果的整数部分被截断。

--所以，最终精度如下：

SELECT 46-(46-38) AS 结果精度,22-(46-38) AS 结果小数位数

结果精度结果小数位数

----------- -----------

38 14

(1 行受影响)

--4.除法

--示例：

SELECT @e1/@e2 AS 结果,LEN(@e1/@e2)-CHARINDEX('.',@e1/@e2) AS 结果小数位数

结果结果小数位数

--------------------------------------- -----------

0.1000000000000000 16

(1 行受影响)

--说明：

--结果精度：p1 - s1 + s2 + max(6, s1 + p2 + 1), 结果小数位数：max(6, s1 + p2 + 1) ： --公式

--结果精度：- 10 + 12 + max(6, 10 + 25 + 1), 结果小数位数：max(6, 10 + 25 + 1) ，以下是伪值：--替代值

SELECT 20 - 10 + 12 + 36 AS 结果精度,36 AS 结果小数位数

结果精度结果小数位数

----------- -----------

58 36

(1 行受影响)

--由于结果精度和小数位数的绝对最大值为38。

--当结果精度大于38 时，相应的小数位数会减少，以避免结果的整数部分被截断。

--所以，最终精度如下：

SELECT 58-(58-38) AS 结果精度,36-(58-38) AS 结果小数位数

结果精度结果小数位数

----------- -----------

38 16

(1 行受影响)

--5.联合，差集，交集

--示例：

SELECT 结果,LEN(结果)-CHARINDEX('.',结果) AS 结果小数位数

FROM (

SELECT @e1 AS 结果

UNION

SELECT CAST(@e2/10 AS DECIMAL(25,12))

) t

结果结果小数位数

--------------------------------------- -----------

10.000000000000 12

(1 行受影响)

SELECT 结果,LEN(结果)-CHARINDEX('.',结果) AS 结果小数位数

FROM (

SELECT @e1 AS 结果

EXCEPT

SELECT @e2

) t

结果结果小数位数

--------------------------------------- -----------

10.000000000000 12

(1 行受影响)

SELECT 结果,LEN(结果)-CHARINDEX('.',结果) AS 结果小数位数

FROM (

SELECT @e1 AS 结果

INTERSECT

SELECT CAST(@e2/10 AS DECIMAL(25,12))

) t

结果结果小数位数

--------------------------------------- -----------

10.000000000000 12

(1 行受影响)

--说明：

--结果精度：max(s1, s2) + max(p1-s1, p2-s2)，结果小数位数：max(s1, s2) --公式

--结果精度：max(10, 12) + max(20-10, 25-12)，结果小数位数：max(10, 12) --替代值

SELECT 12+13 AS 结果精度,12 AS 结果小数位数

结果精度结果小数位数

----------- -----------

25 12

(1 行受影响)

--6.取模

--示例：

SELECT @e1%@e2 AS 结果, LEN(@e1%@e2)-CHARINDEX('.',@e1%@e2) AS 结果小数位数

结果结果小数位数

--------------------------------------- -----------

10.000000000000 12

(1 行受影响)

--说明：

--结果精度：min(p1-s1, p2-s2) + max( s1,s2 )，结果小数位数：max(s1, s2) --公式

--结果精度：min(20-10, 25-12) + max( 10,12 )，结果小数位数：max(10, 12) --替代值

SELECT 13+12 AS 结果精度,12 AS 结果小数位数

结果精度结果小数位数

----------- -----------

25 12

(1 行受影响)