if object_id('tb') is not null
drop table tbcreate 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',3if object_id('devices') is not null
drop table devicescreate table devices(selfNO varchar(10),assetNO varchar(20),depreciation numeric(18, 3))
insert devices
select 'z11','m11',50.0 union all
select 'z12','m12',20.0 union all
select 'z13','m13',10.0
godeclare @sql nvarchar(4000)
set @sql='select row_number() over(order by tb.[deviceNo],tb.[type]) as [序号],'
set @sql=@sql+'tb.deviceNo as [设备编号],devices.assetNO as [资产编号],devices.depreciation as [月折旧费],'
set @sql=@sql+'tb.[type] as [型号],tb.accessoryNo as [零件号],(sum(lastTime)) as [持续时间],'
set @sql=@sql+'1.0*sum(lastTime)/temptable.tempcount as [折旧率],'
set @sql=@sql+'1.0*sum(lastTime)/temptable.tempcount*devices.depreciation as [折旧费]'
set @sql=@sql+'from tb,devices,(select deviceNo,(sum(lastTime)) as tempcount from tb group by deviceNo ) as temptable where tb.deviceNO = devices.selfNO and temptable.deviceNo=devices.selfNO'
set @sql=@sql+' group by tb.accessoryNO,tb.[type],tb.deviceNo,devices.assetNO,devices.depreciation,temptable.tempcount'
exec (@sql)drop table tb
drop table devices现在的执行结果是序号 设备编号 资产编号 月折旧费 型号 零件号 持续时间 折旧率 折旧费
---------- -------------------- --------------------- ---------------- -------------------- ----------- --------------------------------------------
1 z11 m11 50.000 A s10 19 0.575757575757 (19/(14+19)) 28.7878787879 即(19/(14+19)) *50
2 z11 m11 50.000 B s10 14 0.424242424242 (14/(14+19)) 21.2121212121 即 (14/(14+19)) *50
3 z12 m12 20.000 C s11 5 1.000000000000 5/5 20.0000000000 5/5*20
4 z13 m13 10.000 A s10 9 1.000000000000 9/9 10.0000000000 9/9*10现在要求的结果是 即(不按照设备分组显示,将所有的折旧都放到零件号上面)序号 型号 零件号 折旧分摊
1 A s10 38.7878 即(19/(14+19)*50+9/(9)*10)2 B s10 21.2121212121 即(14/(14+19)*50)
3 C s11 20.0000000000 即(5/5)*20
drop table tbcreate 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',3if object_id('devices') is not null
drop table devicescreate table devices(selfNO varchar(10),assetNO varchar(20),depreciation numeric(18, 3))
insert devices
select 'z11','m11',50.0 union all
select 'z12','m12',20.0 union all
select 'z13','m13',10.0
godeclare @sql nvarchar(4000)
set @sql='select row_number() over(order by tb.[deviceNo],tb.[type]) as [序号],'
set @sql=@sql+'tb.deviceNo as [设备编号],devices.assetNO as [资产编号],devices.depreciation as [月折旧费],'
set @sql=@sql+'tb.[type] as [型号],tb.accessoryNo as [零件号],(sum(lastTime)) as [持续时间],'
set @sql=@sql+'1.0*sum(lastTime)/temptable.tempcount as [折旧率],'
set @sql=@sql+'1.0*sum(lastTime)/temptable.tempcount*devices.depreciation as [折旧费]'
set @sql=@sql+'from tb,devices,(select deviceNo,(sum(lastTime)) as tempcount from tb group by deviceNo ) as temptable where tb.deviceNO = devices.selfNO and temptable.deviceNo=devices.selfNO'
set @sql=@sql+' group by tb.accessoryNO,tb.[type],tb.deviceNo,devices.assetNO,devices.depreciation,temptable.tempcount'
exec (@sql)drop table tb
drop table devices现在的执行结果是序号 设备编号 资产编号 月折旧费 型号 零件号 持续时间 折旧率 折旧费
---------- -------------------- --------------------- ---------------- -------------------- ----------- --------------------------------------------
1 z11 m11 50.000 A s10 19 0.575757575757 (19/(14+19)) 28.7878787879 即(19/(14+19)) *50
2 z11 m11 50.000 B s10 14 0.424242424242 (14/(14+19)) 21.2121212121 即 (14/(14+19)) *50
3 z12 m12 20.000 C s11 5 1.000000000000 5/5 20.0000000000 5/5*20
4 z13 m13 10.000 A s10 9 1.000000000000 9/9 10.0000000000 9/9*10现在要求的结果是 即(不按照设备分组显示,将所有的折旧都放到零件号上面)序号 型号 零件号 折旧分摊
1 A s10 38.7878 即(19/(14+19)*50+9/(9)*10)2 B s10 21.2121212121 即(14/(14+19)*50)
3 C s11 20.0000000000 即(5/5)*20
from (
select tb.type,tb.accessoryNo,tb.deviceNo,devices.depreciation
,lastTime=SUM(tb.lastTime)
,sum_lastTime=(SELECT SUM(x.lastTime+0.0) FROM tb AS x WHERE x.deviceNo=tb.deviceNo )
from tb inner join devices on tb.deviceNo=devices.selfNO
group by tb.type,tb.accessoryNo,tb.deviceNo,devices.depreciation
) as a
group by a.type,a.accessoryNo
这个结果的运行结果是(3 行受影响)
序号 型别 零件号 持续时间 设备分配额
-------------------- -------------------- -------------------- ----------- ---------------------------------------
1 A s10 28 38.78787878788
2 B s10 14 21.21212121212
3 C s11 5 20.00000000000(3 行受影响)我想在后面加一行是前面结果的累积该怎么做啊?
0 全部型别 全部零件号 47 38.78787878788+21.21212121212+20.00000000000