sql 2005 数据查询统计大数计算精确度不准确 不知道什么原因 有点费解

[volkswageos]

if object_id('tb') is not null 
drop table tb 

create table tb(type varchar(20),accessoryNo varchar(20),deviceNo varchar(10),lastTime int) 
insert tb 
select 'A','s10','z11',10 union all 
select 'B','s10','z11',11 union all 
select 'C','s11','z12',5 union all 
select 'A','s10','z11',9 union all 
select 'A','s10','z13',9 union all 
select 'B','s10','z11',3 

if object_id('fees') is not null 
drop table fees

create table fees(id int,fee numeric(18, 3))
insert fees 
select 1, 75188.580 
go 

declare @sql1 nvarchar(4000) 
declare @count int
declare @sql nvarchar(4000) 

set @sql1=N'select @a=sum(lastTime)  from tb'
exec sp_executesql @sql1,N'@a int output',@count output
set @sql='select row_number() over(order by [type]) as [序号],[type] as [型号],accessoryNo as [零件号],sum(lastTime) as [持续时间],1.0*sum(lastTime)/'+cast(@count as varchar(20))+'*fee as [分配额
]'
set @sql=@sql+' from tb,fees where id = 1 group by type,accessoryNO,fee' 
set @sql=@sql+' union all select '''' as 序号,''全部型别'' as [型号] ,''全部零件'' as [零件号],sum(lastTime)  as [持续时间],1.0*sum(lastTime)/'+cast(@count as varchar(20))+'*fee as [分配额] from tb,fees group by fee'
exec(@sql) 

drop table tb 
drop table fees

--查询结果是

/*

(1 行受影响)
序号         型号        零件号        持续时间        分配额
-------------------- -------------------- -------------------- ----------- ---------------------------------------
1           A             s10           28          44793.145403520    即75188.58/47*28=44793.196596
2           B             s10           14          22396.572701760    即75188.58/47*14=22396.598297872
3           C             s11           5           7998.711517560     即75188.58/47*5=7998.785106383
0       全部型别        全部零件        47          75188.580000000

(4 行受影响)

即后面是我用计算器计算的

*/

不知道为什么会有不精确的情况，只要是75188.580000000这个数值很大，小数点后的数据就不准确的，如果小的话就没有问题

[--小F--]

隐式转换成了float型的...所以...

[zhengduan964532]

declare @sql1 nvarchar(4000) 
declare @count int
declare @sql nvarchar(4000) 

set @sql1=N'select @a=sum(lastTime)  from tb'
exec sp_executesql @sql1,N'@a int output',@count output
set @sql='select row_number() over(order by [type]) as [序号],[type] as [型号],accessoryNo as [零件号],sum(lastTime) as [持续时间],cast(1.0*sum(lastTime)/'+cast(@count as varchar(20))+'*fee as decimal(18,3)) as [分配额
]'
set @sql=@sql+' from tb,fees where id = 1 group by type,accessoryNO,fee' 
set @sql=@sql+' union all select '''' as 序号,''全部型别'' as [型号] ,''全部零件'' as [零件号],sum(lastTime)  as [持续时间],cast(1.0*sum(lastTime)/'+cast(@count as varchar(20))+'*fee  as decimal(18,3) )as [分配额] from tb,fees group by fee'
exec(@sql) 

[htl258_Tony]

[Quote=引用 1 楼 fredrickhu 的回复:]
隐式转换成了float型的...所以...
[/Quote]正确

[volkswageos]

[Quote=引用 3 楼 htl258 的回复:]
引用 1 楼 fredrickhu 的回复:
隐式转换成了float型的...所以...
正确
[/Quote]

if object_id('tb') is not null
drop table tb

create table tb(type varchar(20),accessoryNo varchar(20),deviceNo varchar(10),lastTime int)
insert tb
select 'A','s10','z11',10 union all
select 'B','s10','z11',11 union all
select 'C','s11','z12',5 union all
select 'A','s10','z11',9 union all
select 'A','s10','z13',9 union all
select 'B','s10','z11',3

if object_id('fees') is not null
drop table fees

create table fees(id int,fee numeric(18, 6))
insert fees
select 1, 575188.580
go

declare @sql1 nvarchar(4000)
declare @count as decimal(18,6)
declare @sql nvarchar(4000)

set @sql1=N'select @a=sum(lastTime) from tb'
exec sp_executesql @sql1,N'@a int output',@count output
set @sql='select row_number() over(order by [type]) as [序号],[type] as [型号],accessoryNo as [零件号],sum(lastTime) as [持续时间],1.0*sum(lastTime)/'+cast(@count as varchar(30))+'*fee as [分配额
]'
set @sql=@sql+' from tb,fees where id = 1 group by type,accessoryNO,fee'
set @sql=@sql+' union all select '''' as 序号,''全部型别'' as [型号] ,''全部零件'' as [零件号],sum(lastTime) as [持续时间],1.0*sum(lastTime)/'+cast(@count as varchar(30))+'*fee as [分配额] from tb,fees group by fee'
exec(@sql)

drop table tb
drop table fees

/*

(1 行受影响)
序号 型号 零件号 持续时间 分配额
-------------------- -------------------- -------------------- ----------- ---------------------------------------
1 A s10 28 342665.5369919 即575188.5800000/47*28=342665.53702128
2 B s10 14 171332.7684960 即575188.5800000/47*14=171332.76851064
3 C s11 5 61190.2744546 即
575188.5800000/47*14=61190.274468085
0 全部型别 全部零件 47 575188.5800000

(4 行受影响)
*/

/*
即后面的数据是用计算器计算的，有点差距的，当数值比较大的时候就有了误差
*/

[ACMAIN_CHM]

没办法，计算机在计算小数（浮点数）的时候会产生误差。没有办法的事儿。

[永生天地]

请lz注意一下，运算符先后顺序 fee*放前面，因为类型转换以优先“/”前面的

if object_id('tb') is not null 
drop table tb 

create table tb(type varchar(20),accessoryNo varchar(20),deviceNo varchar(10),lastTime int) 
insert tb 
select 'A','s10','z11',10 union all 
select 'B','s10','z11',11 union all 
select 'C','s11','z12',5 union all 
select 'A','s10','z11',9 union all 
select 'A','s10','z13',9 union all 
select 'B','s10','z11',3 

if object_id('fees') is not null 
drop table fees

create table fees(id int,fee numeric(18, 3))
insert fees 
select 1, 75188.580 
go 

declare @sql1 nvarchar(4000) 
declare @count int
declare @sql nvarchar(4000) 

set @sql1=N'select @a=sum(lastTime)  from tb'
exec sp_executesql @sql1,N'@a int output',@count output
set @sql='select row_number() over(order by [type]) as [序号],[type] as [型号],accessoryNo as [零件号],sum(lastTime) as [持续时间],fee*sum(lastTime)/'+cast(@count as varchar(20))+' as [分配额
]'
set @sql=@sql+' from tb,fees where id = 1 group by type,accessoryNO,fee' 
set @sql=@sql+' union all select '''' as 序号,''全部型别'' as [型号] ,''全部零件'' as [零件号],sum(lastTime)  as [持续时间],fee*sum(lastTime)/'+cast(@count as varchar(20))+' as [分配额] from tb,fees group by fee'
exec(@sql) 

drop table tb 
drop table fees




（所影响的行数为 6 行）


（所影响的行数为 1 行）

序号                   型号                   零件号                  持续时间        分配额
                              
-------------------- -------------------- -------------------- ----------- ---------------------------------- 
1                    A                    s10                  28          44793.196595
2                    B                    s10                  14          22396.598297
3                    C                    s11                  5           7998.785106
0                    全部型别                 全部零件                 47          75188.580000

（所影响的行数为 4 行）

[volkswageos]

[Quote=引用 6 楼 xys_777 的回复:]
请lz注意一下，运算符先后顺序 fee*放前面，因为类型转换以优先“/”前面的


SQL code

if object_id('tb') is not null
drop table tb

create table tb(type varchar(20),accessoryNo varchar(20),deviceNo varchar(10),lastTime int) ……
[/Quote]


还是没有明白你是什么意思，不过你是对的

[tronrich]

float类型是有这问题，我一般都四舍五入到三位小数

[baimuer001]

csdn 变得很流氓了，看个帖子还得回复