如下的语句,为什么Rate这列,返回值是直接6位小数,且得到的是“截取”的6位小数,而不是四舍五入的,
而Rate2 却能返回正确的多位小数。 select SumPrice = (SELECT SUM(CitPrice) FROM #BOM U0 WHERE U0.Father = #BOM.Father),
Rate = CitPrice / (SELECT SUM(CitPrice) FROM #BOM U0 WHERE U0.Father = #BOM.Father) ,
citprice,
SQL_VARIANT_PROPERTY (citprice,'PRECISION') 分子精度,
SQL_VARIANT_PROPERTY ( (SELECT SUM(CitPrice) FROM #BOM U0 WHERE U0.Father = #BOM.Father),'PRECISION') 分母精度 ,
Rate2 = convert(numeric(28,15), CitPrice) / (SELECT SUM(CitPrice) FROM #BOM U0 WHERE U0.Father = #BOM.Father) ,
SQL_VARIANT_PROPERTY ( convert(numeric(28,15), CitPrice) ,'PRECISION') 分子2精度 FROM #BOM where Father='1020018'
结果如下:
SumPrice Rate CitPrice 分子精度 分母精度 Rate2 分子2精度250.266195 0.194424 48.657800 38 38 0.1944241810205329569 28
250.266195 0.012820 3.208600 38 38 0.0128207487231745382 28
250.266195 0.017279 4.324390 38 38 0.0172791614944239672 28
250.266195 0.578923 144.885011 38 38 0.5789236177103343901 28
250.266195 0.157197 39.341234 38 38 0.1571975551871877861 28
250.266195 0.023592 5.904460 38 38 0.0235927189447220388 28
250.266195 0.015762 3.944700 38 38 0.0157620169196243224 28
而Rate2 却能返回正确的多位小数。 select SumPrice = (SELECT SUM(CitPrice) FROM #BOM U0 WHERE U0.Father = #BOM.Father),
Rate = CitPrice / (SELECT SUM(CitPrice) FROM #BOM U0 WHERE U0.Father = #BOM.Father) ,
citprice,
SQL_VARIANT_PROPERTY (citprice,'PRECISION') 分子精度,
SQL_VARIANT_PROPERTY ( (SELECT SUM(CitPrice) FROM #BOM U0 WHERE U0.Father = #BOM.Father),'PRECISION') 分母精度 ,
Rate2 = convert(numeric(28,15), CitPrice) / (SELECT SUM(CitPrice) FROM #BOM U0 WHERE U0.Father = #BOM.Father) ,
SQL_VARIANT_PROPERTY ( convert(numeric(28,15), CitPrice) ,'PRECISION') 分子2精度 FROM #BOM where Father='1020018'
结果如下:
SumPrice Rate CitPrice 分子精度 分母精度 Rate2 分子2精度250.266195 0.194424 48.657800 38 38 0.1944241810205329569 28
250.266195 0.012820 3.208600 38 38 0.0128207487231745382 28
250.266195 0.017279 4.324390 38 38 0.0172791614944239672 28
250.266195 0.578923 144.885011 38 38 0.5789236177103343901 28
250.266195 0.157197 39.341234 38 38 0.1571975551871877861 28
250.266195 0.023592 5.904460 38 38 0.0235927189447220388 28
250.266195 0.015762 3.944700 38 38 0.0157620169196243224 28
SQL的计算逻辑整不明白
select 1.0/3
select 1.0/3
/*
---------------------------------------
0.333333(1 行受影响)
*/
select convert(numeric(28,15),1.0/3 )
/*
---------------------------------------
0.333333000000000(1 行受影响)
*/select Cast(1.0/3 as decimal(18, 9))
/*
---------------------------------------
0.333333000(1 行受影响)
*/以前都没发现,怎么修改SQL的这个默认值?
select 1.000000/3---------------------------------------
0.333333(1 行受影响)
---------------------------------------
0.33333333(1 行受影响)
我觉得是“精度”的原因,但不知道规则是什么,还请高手解释呀
drop table t;create table t(
--cityprice float
--cityprice numeric(18,6)
cityprice decimal(18,6)
)insert into t select 48.657800 union all
select 3.208600 union all
select 4.324390 union all
select 144.885011 ;select * from t;select cityprice/(select SUM(cityprice) from t) from t;-- 楼主cityprice是什么类型,我用了几种都不是你那种效果。
-- 是不是你把选出的结果查入到另一个表中了?
SQL Server精度数据(decimal和numeric)在算术运算时的自动转换规则