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

冷不防 2022-09-30 09:47 144阅读 0赞

精度、小数位数和长度 (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.csdn.net/htl258][http_blog.csdn.net_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 行受影响)

\*/

本文来自CSDN博客，转载请标明出处：[http://blog.csdn.net/htl258/archive/2010/07/06/5715979.aspx][http_blog.csdn.net_htl258_archive_2010_07_06_5715979.aspx]

[http_blog.csdn.net_htl258]: http://blog.csdn.net/htl258
[http_blog.csdn.net_htl258_archive_2010_07_06_5715979.aspx]: http://blog.csdn.net/htl258/archive/2010/07/06/5715979.aspx