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 decimal(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 decimal(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
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 行受影响)
其中折旧费计算不精确实际应该是（上面的查询应该怎么修改啊）
序号 设备编号 资产编号 月折旧费 型号 零件号 持续时间 折旧率 折旧费
-------------------- ---------- -------------------- --------------------------------------- -------------------- -------------------- ----------- --------------------------------------- ---------------------------------------
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
...全文
请发表友善的回复…
发表回复
永生天地 2010-04-08
- 打赏
- 举报
常数1.0 是decmial类型，其中的小数位数可以决定运算后的小数位数
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
*/
