if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test1]
GOCREATE TABLE [dbo].[test1] (
[ft1] [numeric](18, 9) NULL ,
[ft2] [numeric](18, 9) NULL ,
[ft3] [numeric](18, 9) NULL
) ON [PRIMARY]
GO insert into test1 (ft1,ft2,ft3)
select 1.38974,1.34456,2.27
-----
select ft1*ft2 *ft3 from test1
只能得到4.241696609,而实际应该是4.241696608688
该如何取到正确的值呢
drop table [dbo].[test1]
GOCREATE TABLE [dbo].[test1] (
[ft1] [numeric](18, 9) NULL ,
[ft2] [numeric](18, 9) NULL ,
[ft3] [numeric](18, 9) NULL
) ON [PRIMARY]
GO insert into test1 (ft1,ft2,ft3)
select 1.38974,1.34456,2.27
-----
select ft1*ft2 *ft3 from test1
只能得到4.241696609,而实际应该是4.241696608688
该如何取到正确的值呢
CREATE TABLE [dbo].[test1] (
[ft1] [numeric](18, 12) NULL ,
[ft2] [numeric](18, 12) NULL ,
[ft3] [numeric](18, 12) NULL
) ON [PRIMARY]
GO insert into test1 (ft1,ft2,ft3)
select 1.38974,1.34456,2.27select ft1*ft2 *ft3 from test1drop table test1/*
----------------------------------------
4.241696608688000000(所影响的行数为 1 行)
*/
CREATE TABLE [dbo].[test1] (
[ft1] [numeric](18, 9) NULL ,
[ft2] [numeric](18, 9) NULL ,
[ft3] [numeric](18, 9) NULL
) ON [PRIMARY]
GOinsert into test1 (ft1,ft2,ft3)
select 1.38974,1.34456,2.27select cast(cast(ft1*ft2 as decimal(18,12)) * ft3 as decimal(18,12)) from test1drop table test1/*
--------------------
4.241696608688(所影响的行数为 1 行)
*/
(爱新觉罗.毓华)
方法可行
但原因是甚磨?如果一个复杂的计算我还必须每处理一步就处理下小数位吗?是否有简单处理的方法?
比如:select ft1*ft2 *ft3/((1-ft3)*FT2)+FT1
这样每步处理,就烦琐了
设 @n1 numeric(p1,s1) ,@n2 numeric(p2,s2),
当 相乘 时,其结果的精度,是
p = p1+p2+1
s = s1+s2
这里需要注意的是,当p1+p2+1>38时,会自动减少小数位,以防止整数部分被截断。(至于如何较少小数位,暂不知)測試語句declare @n1 numeric(28,8),@n2 numeric(28,8),@n3 numeric(28,8) , @n4 sql_variant
select @n1=1.0, @n2=0.0002046
select @n3=@n1*@n2 , @n4 =@n1*@n2select @n3 as n3, @n4 as n4,
Type=sql_variant_property(@n4,'basetype'),
[precision] =sql_variant_property(@n4,'precision'),
[scale] =sql_variant_property(@n4,'scale')/*
-- look , @n4的type 編程了 numeric(38,6),也就是p的部份是38,而s被截斷了
0.00020500 0.000205 numeric 38 6*/
declare @result numeric(18,12)
然后再
select @result=ft1*ft2 *ft3 from test1
应该可以.