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 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('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',50.0 union all
select 'z12','m12',20.0 union all
select 'z13','m13',10.0
go
declare @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