22,209
社区成员
发帖
与我相关
我的任务
分享sql 2005 查询结果数据不精确问题 疑难
if object_id('tb') is not null
drop table tb
create table tb(type varchar(20),accessoryNo varchar(20),deviceNo varchar(10),lastTime numeric(18, 3))
insert tb
select 'A','s10','z11',1 union all
select 'B','s10','z11',8 union all
select 'C','s11','z12',5 union all
select 'C','s11','z11',2 union all
select 'A','s10','z11',0 union all
select 'A','s10','z13',9 union all
select 'B','s10','z11',0
if object_id('devices') is not null
drop table devices
create table devices(selfNO varchar(10),assetNO varchar(20),depreciation numeric(18, 3))
insert devices
select 'z11','m11',23621.79 union all
select 'z12','m12',20.0 union all
select 'z13','m13',10.0
go
select row_number() over(order by [deviceNo],[type]) as [序号],
deviceNo as [设备编号],
assetNO as [资产编号],
depreciation as [月折旧费],
[type] as [型号],
accessoryNo as [零件号],
sum(lastTime) as [持续时间],
折旧率=cast(sum(lastTime)*1.0/(select sum(lastTime) from tb where t.deviceNo=deviceNo) as decimal(27,6)),
折旧费=cast(sum(lastTime)*1.0/(select sum(lastTime) from tb where t.deviceNo=deviceNo)*depreciation as decimal(27,6))
from tb t,devices
where deviceNO = selfNO
group by type,accessoryNO,deviceNo,assetNO,depreciation
drop table tb
drop table devices
执行结果
序号 设备编号 资产编号 月折旧费 型号 零件号 持续时间 折旧率 折旧费
-------------------- ---------- -------------------- --------------------------------------- -------------------- -------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1 z11 m11 23621.790 A s10 1.000 0.090909 2147.433307 用计算器为 2147.435455
2 z11 m11 23621.790 B s10 8.000 0.727272 17179.466457
3 z11 m11 23621.790 C s11 2.000 0.181818 4294.866614
4 z12 m12 20.000 C s11 5.000 1.000000 20.000000
5 z13 m13 10.000 A s10 9.000 1.000000 10.000000
(5 行受影响)
下面的这个只要把lastTime 弄成int结果是没有误差，但是我的lastTime必须是带有小数的
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',1 union all
select 'B','s10','z11',8 union all
select 'C','s11','z12',5 union all
select 'C','s11','z11',2 union all
select 'A','s10','z11',0 union all
select 'A','s10','z13',9 union all
select 'B','s10','z11',0
if object_id('devices') is not null
drop table devices
create table devices(selfNO varchar(10),assetNO varchar(20),depreciation numeric(18, 3))
insert devices
select 'z11','m11',23621.79 union all
select 'z12','m12',20.0 union all
select 'z13','m13',10.0
go
select row_number() over(order by [deviceNo],[type]) as [序号],
deviceNo as [设备编号],
assetNO as [资产编号],
depreciation as [月折旧费],
[type] as [型号],
accessoryNo as [零件号],
sum(lastTime) as [持续时间],
折旧率=cast(sum(lastTime)*1.0/(select sum(lastTime) from tb where t.deviceNo=deviceNo) as decimal(27,6)),
折旧费=cast(sum(lastTime)*1.0/(select sum(lastTime) from tb where t.deviceNo=deviceNo)*depreciation as decimal(27,6))
from tb t,devices
where deviceNO = selfNO
group by type,accessoryNO,deviceNo,assetNO,depreciation
drop table tb
drop table devices
执行结果是
(3 行受影响)
序号 设备编号 资产编号 月折旧费 型号 零件号 持续时间 折旧率 折旧费
-------------------- ---------- -------------------- --------------------------------------- -------------------- -------------------- ----------- --------------------------------------- ---------------------------------------
1 z11 m11 23621.790 A s10 1 0.090909 2147.435455
2 z11 m11 23621.790 B s10 8 0.727273 17179.483636
3 z11 m11 23621.790 C s11 2 0.181818 4294.870909
4 z12 m12 20.000 C s11 5 1.000000 20.000000
5 z13 m13 10.000 A s10 9 1.000000 10.000000
(5 行受影响)
...全文
请发表友善的回复…
发表回复
htl258_Tony 2010-04-08
- 打赏
- 举报
if object_id('tb') is not null
drop table tb
create table tb(type varchar(20),accessoryNo varchar(20),deviceNo varchar(10),lastTime numeric(18, 3))
insert tb
select 'A','s10','z11',1 union all
select 'B','s10','z11',8 union all
select 'C','s11','z12',5 union all
select 'C','s11','z11',2 union all
select 'A','s10','z11',0 union all
select 'A','s10','z13',9 union all
select 'B','s10','z11',0
if object_id('devices') is not null
drop table devices
create table devices(selfNO varchar(10),assetNO varchar(20),depreciation numeric(18, 3))
insert devices
select 'z11','m11',23621.79 union all
select 'z12','m12',20.0 union all
select 'z13','m13',10.0
go
select row_number() over(order by [deviceNo],[type]) as [序号],
deviceNo as [设备编号],
assetNO as [资产编号],
depreciation as [月折旧费],
[type] as [型号],
accessoryNo as [零件号],
sum(lastTime) as [持续时间],
折旧率=cast(cast(sum(lastTime)*1.0 as decimal(27,6))/(select sum(lastTime) from tb where t.deviceNo=deviceNo) as decimal(27,6)),
折旧费=cast(cast(sum(lastTime)*1.0 as decimal(27,6))/(select sum(lastTime) from tb where t.deviceNo=deviceNo)*depreciation as decimal(27,6))
from tb t,devices
where deviceNO = selfNO
group by type,accessoryNO,deviceNo,assetNO,depreciation
drop table tb
drop table devices
/*
序号 设备编号 资产编号 月折旧费 型号 零件号 持续时间 折旧率 折旧费
1 z11 m11 23621.790 A s10 1.000 0.090909 2147.435455
2 z11 m11 23621.790 B s10 8.000 0.727273 17179.483636
3 z11 m11 23621.790 C s11 2.000 0.181818 4294.870909
4 z12 m12 20.000 C s11 5.000 1.000000 20.000000
5 z13 m13 10.000 A s10 9.000 1.000000 10.000000
*/
volkswageos 2010-04-08
- 打赏
- 举报
[Quote=引用 5 楼 xys_777 的回复:]
1.000000000多写些0
只能先这么用，不知道sqlserver那里怎么转换的不一样了
[/Quote]
多谢0也不好使啊，真的不行的，我试过了
1.000000000多写些0
只能先这么用，不知道sqlserver那里怎么转换的不一样了
[/Quote]
多谢0也不好使啊，真的不行的，我试过了
永生天地 2010-04-08
- 打赏
- 举报
1.000000000多写些0
只能先这么用，不知道sqlserver那里怎么转换的不一样了
只能先这么用，不知道sqlserver那里怎么转换的不一样了
luoyoumou 2010-04-07
- 打赏
- 举报
-- 呵呵：楼主没穿短裤？
ACMAIN_CHM 2010-04-07
- 打赏
- 举报
计算机中浮点数运算是有误差的。
就如同不能期望
1/3 * 3 = 1 一样。
就如同不能期望
1/3 * 3 = 1 一样。
volkswageos 2010-04-07
- 打赏
- 举报
[Quote=引用 1 楼 fredrickhu 的回复:]
没搞懂 上面的不就行了么?
[/Quote]
上面那个查询折旧费有误差，下面的没有误差，但是我的计算里面lastTime必须是带有小数点的，不知道是什么原因导致的计算有误差的
没搞懂 上面的不就行了么?
[/Quote]
上面那个查询折旧费有误差，下面的没有误差，但是我的计算里面lastTime必须是带有小数点的，不知道是什么原因导致的计算有误差的
--小F-- 2010-04-07
- 打赏
- 举报
没搞懂 上面的不就行了么?
