SQL计算保留精度,弄死我了

mycool2006 2009-06-02 04:54:22
每月所有物料按平均价出库
本月出库单价=(期初金额+本月总入库金额)/(期初数量+本月总入库数量)
然后保留6位小数的单价,本月所有的出库金额=出库数量*单价
本月结存数量=(期初数量+总入-总出)
本月结存金额=(期初金额+总入金额-(单价*出库数量))

select sum(63883.63)/sum(23625) --这是我用分组计算出的单价
select 63883.63/23625 --手动算出的单价格

结果分别为:
2.704068
2.70406899

我需要单价保留6位小数的,经过四舍五入以后,结果不一样,一个是 2.704068 一个是2.704069。 通过这样的计算,最终结存金额相差 2分钱 开始死活找不到原因,不知道大家有没有遇到过这种事情,下次要多注意注意。


我是通过select sum(1.000000000*63883.63)/sum(23625) 来解决的 请问有没有好的方式
...全文
1329 29 打赏 收藏 转发到动态 举报
写回复
用AI写文章
29 条回复
切换为时间正序
请发表友善的回复…
发表回复
playwarcraft 2009-06-03
  • 打赏
  • 举报
回复
select sum(63883.63)/sum(23625)

在這個表达式里,sql server 默认 sum(6388.3.63) 為 numeric(38,2) , sum(23625) 為int(10,0)
所以 当 p1(38)+p2(10)+1 >38 時,就自動截斷 小數位了。
只需要將 其中一個的精度P減小就可以show出更多的小数位

可以玩下
declare @n4 sql_variant
select @n4= convert(numeric(33,2),sum(63883.63))/sum(23625)
select type=SQL_Variant_property(@n4,'BaseType'),
[precision]=SQL_Variant_property(@n4,'Precision'),
[scale]=SQL_Variant_property(@n4,'scale')
select @n4

红色数字变动,会发现结果不一样的。。。
playwarcraft 2009-06-03
  • 打赏
  • 举报
回复
看这个例子:

declare @n1 numeric(28,8), @n2 numeric(28,8),@n3 numeric(28,8)
select @n1=1.0 ,@n2=0.0002046
select @n3=@n1*@n2
select @n3
/*
result:
@n3= 0.00020500 (而不是0.0002046 因為小數位被截斷,只取到6位小數)
*/

--原因:
declare @n4 sql_variant
select @n4=@n1*@n2
select Type=SQL_Variant_property(@n4,'BaseType'),
[Precision]=SQL_Variant_property(@n4,'Precision'),
[scale]=SQL_Variant_property(@n4,'scale')

/*
小數位被截斷了。
type precision scale
--------------------------
numeric 38 6
*/

肥龙上天 2009-06-03
  • 打赏
  • 举报
回复
      create table #temp 
(
[operatorid] [int] NULL ,
[storehouseid] [int] NULL ,
[productid] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[summoney] [decimal](18, 9) NULL , --精度 4 改成6 翁
[sumamount] [decimal](18, 6) NULL ,--精度 4 改成6 翁
[price] [decimal](18,9) NULL --精度 4 改成6 翁
)
insert #temp(productid,storehouseid,sumamount,summoney,price)
select productid,@houseid,sum(amount) as amount,sum(summoney) as summoney ,
case when sum(amount)=0 then 0 else cast(sum(summoney)/sum(amount) as decimal(18,6)) end as Price
from
(
select itemid,productid,amount,summoney from p_productAccountSumTab where itemid=@endid and houseid=@houseid
union all
select '1',productid,sum(amount) as amount,sum(1.00000000*money) as money from kc_billjoinmaintab a
join kc_billjoinlisttab b on a.billdocument_id=b.billdocument_id
where operatedate>=@stdt and operatedate<@eddte+1 and storehouseid=@houseid group by productid
) a
group by productid
playwarcraft 2009-06-03
  • 打赏
  • 举报
回复
看13樓的解釋,
当你没指定数值类型和精度时,sql server会默认帮你选一个,
然后受精度38的限制
肥龙上天 2009-06-03
  • 打赏
  • 举报
回复
select cast(63883.63 as decimal(18,7))/23625
/*

---------------------------------------
2.7040689947089

(1 row(s) affected)
*/
mycool2006 2009-06-03
  • 打赏
  • 举报
回复
[Quote=引用 23 楼 you_tube 的回复:]
引用 22 楼 mycool2006 的回复:
SQL code--方式1计算结果declare@returnvaluedecimal(18,6),@startamountdecimal(18,6),@startmoneydecimal(18,6),@inamountdecimal(18,6),@inmoneydecimal(18,6)if@startamount+@inamount <>0set@returnvalue=ROUND((@startmoney+@inmoney)/(@startamount+@inamount),6)--通过上面这种方式计算出来的结果,会按第7位小数四舍五入--方式2计算结果createtable#temp([operatorid][int]NULL,[storeh…
[/Quote]



没明白为什么
you_tube 2009-06-03
  • 打赏
  • 举报
回复
[Quote=引用 22 楼 mycool2006 的回复:]
SQL code--方式1计算结果declare@returnvaluedecimal(18,6),@startamountdecimal(18,6),@startmoneydecimal(18,6),@inamountdecimal(18,6),@inmoneydecimal(18,6)if@startamount+@inamount<>0set@returnvalue=ROUND((@startmoney+@inmoney)/(@startamount+@inamount),6)--通过上面这种方式计算出来的结果,会按第7位小数四舍五入--方式2计算结果createtable#temp([operatorid][int]NULL,[storehouseid][int]NULL,[productid][var…
[/Quote]
那你加乘个1.000000就行了嘛
mycool2006 2009-06-03
  • 打赏
  • 举报
回复

--方式1计算结果
declare @returnvalue decimal(18,6),
@startamount decimal(18,6),
@startmoney decimal(18,6),
@inamount decimal(18,6),
@inmoney decimal(18,6)
if @startamount+@inamount<>0
set @returnvalue=ROUND((@startmoney+@inmoney)/(@startamount+@inamount),6)
--通过上面这种方式计算出来的结果,会按第7位小数四舍五入

--方式2计算结果
create table #temp
(
[operatorid] [int] NULL ,
[storehouseid] [int] NULL ,
[productid] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[summoney] [decimal](18, 9) NULL , --精度 4 改成6 翁
[sumamount] [decimal](18, 6) NULL ,--精度 4 改成6 翁
[price] [decimal](18,9) NULL --精度 4 改成6 翁
)
insert #temp(productid,storehouseid,sumamount,summoney,price)
select productid,@houseid,sum(amount) as amount,sum(summoney) as summoney ,
case when sum(amount)=0 then 0 else round(sum(1.00000000*summoney)/sum(amount),6) end Price from --单价取6位小数 翁
(select itemid,productid,amount,summoney from p_productAccountSumTab where itemid=@endid and houseid=@houseid union all --这里增加了 all 翁
select '1',productid,sum(amount) as amount,sum(1.00000000*money) as money from kc_billjoinmaintab a
join kc_billjoinlisttab b on a.billdocument_id=b.billdocument_id
where operatedate>=@stdt and operatedate<@eddte+1 and storehouseid=@houseid group by productid) a
group by productid



最关键是就是红色的地方,如果我不*1.00000000 临时表中计算出来的结果price就是第7位小数不会四舍五入
mycool2006 2009-06-03
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 sdhdy 的回复:]
SQL codeselect cast(sum(63883.63)/sum(23625) as decimal(18,6))

select sum(63883.63)*1.000000/sum(23625)
[/Quote]



select sum(63883.63)*1.00000000000/sum(23625)
2.704068
ai_li7758521 2009-06-02
  • 打赏
  • 举报
回复
[Quote=引用 15 楼 happyflystone 的回复:]
select sum(cast(63883.63 as numeric(12,6)))/sum(23625)
[/Quote]up
Tomzzu 2009-06-02
  • 打赏
  • 举报
回复
LZ, 这是MSSQL计算时中间变量的默认数据类型的问题, 我们干涉不了这个默认数据类型, 但我们可以通过SQL表达式从外围影响它

LZ已经想到一个巧方法, 就是在sum函数里面乘以1.00000000, 注意如果是在sum之外乘以1.00000000是不对的, 在两个sum相除之后, 再做cast或convert转换也是不对的, 因为默认还是6位小数

还有其它写法, 以下无论是保留8位还是6位, 两种select语句结果是一样的, 不存在小数的误差
如果都保留8位如下


select round(cast(sum(63883.63) as dec(30, 8))/sum(23625), 8) --这是我用分组计算出的单价
select 63883.63/23625 --手动算出的单价格


如果都保留6位如下

select round(cast(sum(63883.63) as dec(30, 8))/sum(23625), 6) --这是我用分组计算出的单价
select round(63883.63/23625, 6) --手动算出的单价格
-- 结果如下

----------------------------------------
2.7040690000000000

(所影响的行数为 1 行)


---------------
2.70406900

(所影响的行数为 1 行)




lihan6415151528 2009-06-02
  • 打赏
  • 举报
回复

select cast(sum(63883.63)/sum(23625) as dec(30,6))
JonasFeng 2009-06-02
  • 打赏
  • 举报
回复
[Quote=引用 16 楼 gaojier1000 的回复:]
SQL code楼主打算要那一个结果?
--ROUND
--返回数字表达式并四舍五入为指定的长度或精度。
--
--语法
--ROUND ( numeric_e­xpression , length [ , function ] )
--
--参数
--numeric_e­xpression
--
--精确数字或近似数字数据类型类别的表达式(bit 数据类型除外)。
--
--length
--
--是 numeric_e­xpression 将要四舍五入的精度。length 必须是 tinyint、smallint 或int。当 length 为正数时,numeric_e­xpres…
[/Quote]

学习
  • 打赏
  • 举报
回复
楼主打算要那一个结果?
--ROUND
--返回数字表达式并四舍五入为指定的长度或精度。
--
--语法
--ROUND ( numeric_e­xpression , length [ , function ] )
--
--参数
--numeric_e­xpression
--
--精确数字或近似数字数据类型类别的表达式(bit 数据类型除外)。
--
--length
--
--是 numeric_e­xpression 将要四舍五入的精度。length 必须是 tinyint、smallint 或int。当 length 为正数时,numeric_e­xpression 四舍五入为 length 所指定的小数位数。当 length 为负数时,numeric_e­xpression 则按 length 所指定的在小数点的左边四舍五入。
--
--function
--
--是 要执行的操作类型。function 必须是 tinyint、smallint 或 int。如果省略 function 或 function 的值为 0(默认),numeric_e­xpression 将四舍五入。当指定 0 以外的值时,将截断 numeric_e­xpression。
--
--返回类型
--返回与 numeric_e­xpression 相同的类型。
--
--注释
--ROUND 始终返回一个值。如果 length 是负数且大于小数点前的数字个数,ROUND 将返回 0。
--
--示例 结果
--ROUND(748.58, -4) 0
--
--当 length 是负数时,无论什么数据类型,ROUND 都将返回一个四舍五入的 numeric_e­xpression。
--
--示例 结果
--ROUND(748.58, -1) 750.00
--ROUND(748.58, -2) 700.00
--ROUND(748.58, -3) 1000.00
--
--示例
--A. 使用 ROUND 和估计值
--下例显示两个表达式,说明使用 ROUND 函数且最后一个数字始终是估计值。
--
Select ROUND(123.9994, 3), ROUND(123.9995, 3)
GO

--下面是结果集:
--
--———– ———–
--123.9990 124.0000
--
--B. 使用 ROUND 和四舍五入的近似值
--下例显示四舍五入和近似值。
--
--语句 结果
Select ROUND(123.4545, 2)
--123.4500

Select ROUND(123.45, -2)
--100.00
--
--C. 使用 ROUND 截断
--下例使用两个 Select 语句说明四舍五入和截断之间的区别。第一个语句四舍五入结果。第二个语句截断结果。
--
--语句 结果
Select ROUND(150.75, 0)
--151.00

Select ROUND(150.75, 0, 1)
--150.00
-狙击手- 2009-06-02
  • 打赏
  • 举报
回复
select sum(cast(63883.63 as numeric(12,6)))/sum(23625)
nzperfect 2009-06-02
  • 打赏
  • 举报
回复

在除之前控制精度应该就可以:
select cast(cast(sum(63883.63) as dec(30,8))/cast(sum(23625) as dec(30,8))  as dec(30,6))
playwarcraft 2009-06-02
  • 打赏
  • 举报
回复
这个是sql server对数字型numeric本身的限制,
数字运算decimal[p,s]
当相乘p1+p2+1>38时,会自动减小小数位,以防止整数部分被截断。


declare @n1 sql_variant, @n2 sql_variant
select @n1=sum(63883.63), @n2=sum(23625)
select type=SQL_Variant_property(@n1,'BaseType'),
[precision]=SQL_Variant_property(@n1,'Precision'),
[scale]=SQL_Variant_property(@n1,'scale')

/*
type Precision scale
-------------------------------
numeric 38 2
*/
select type=SQL_Variant_property(@n2,'BaseType'),
[precision]=SQL_Variant_property(@n2,'Precision'),
[scale]=SQL_Variant_property(@n2,'scale')

/*
type Precision scale
-------------------------------
int 10 0
*/

declare @n4 sql_variant
select @n4= sum(63883.63)/sum(23625)
select type=SQL_Variant_property(@n4,'BaseType'),
[precision]=SQL_Variant_property(@n4,'Precision'),
[scale]=SQL_Variant_property(@n4,'scale')
/*
type Precision scale
-------------------------------
numeric 38 6

*/
GO

----如果不用sum
declare @n1 sql_variant, @n2 sql_variant
select @n1=63883.63, @n2=23625
select type=SQL_Variant_property(@n1,'BaseType'),
[precision]=SQL_Variant_property(@n1,'Precision'),
[scale]=SQL_Variant_property(@n1,'scale')
/*
type Precision scale
-------------------------------
numeric 7 2

*/
select type=SQL_Variant_property(@n2,'BaseType'),
[precision]=SQL_Variant_property(@n2,'Precision'),
[scale]=SQL_Variant_property(@n2,'scale')
/*
type Precision scale
-------------------------------
int 10 0
*/
declare @n4 sql_variant
select @n4= 63883.63/23625
select type=SQL_Variant_property(@n4,'BaseType'),
[precision]=SQL_Variant_property(@n4,'Precision'),
[scale]=SQL_Variant_property(@n4,'scale')
/*
type Precision scale
-------------------------------
numeric 13 8
*/

htl258_Tony 2009-06-02
  • 打赏
  • 举报
回复
select cast(cast(sum(63883.63) as dec(28,6))/sum(23625) as dec(28,6))

/*
---------------------------------------
2.704069

(1 行受影响)

*/
sdhdy 2009-06-02
  • 打赏
  • 举报
回复
select cast(sum(63883.63)/sum(23625)  as decimal(18,6))

select sum(63883.63)*1.000000/sum(23625)
张家可 2009-06-02
  • 打赏
  • 举报
回复

保留6位小数
convert(decimal(8,6),(63883.63/23625))

结果为:
2.704069
加载更多回复(9)

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧