sql 2005 数据查询统计疑难问题

Question

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 devicescreate table devices(selfNO varchar(10),assetNO varchar(20),depreciation numeric(18, 3))
insert devices 
select 'z11','m11',100.0 union all 
select 'z12','m12',100.0 union all 
select 'z13','m13',100.0
go declare @sql nvarchar(4000) set @sql='select row_number() over(order by [deviceNo],[type]) as [序号],deviceNo as [设备编号],assetNO as [资产编号],depreciation as [月折旧费],[type] as [型号],accessoryNo as [零件号],sum(lastTime) as [持续时间]'
set @sql=@sql+' from tb,devices where deviceNO = selfNO group by type,accessoryNO,deviceNo,assetNO,depreciation' exec(@sql) drop table tb 
drop table devices
这个是现在的结果
序号     设备编号    资产编号   月折旧    型别       零件号     持续时间
1        z11        m11        100.000        A        s10        19
2        z11        m11        100.000        B        s10        14
3        z12        m12        100.000        C        s11        5
4        z13        m13        100.000        A        s10        9我想要的结果是
再加两个字段
这个是现在的结果
序号 设备编号 资产编号   月折旧    型别       零件号     持续时间 折旧率 折旧费
1        z11        m11        100.000        A        s10        19     19/(19+14)    19/(19+14) *100  
2        z11        m11        100.000        B        s10        14     14/(19+14)    14/(19+14) *100  3        z12        m12        100.000        C        s11        5         5/5      5/5*100
4        z13        m13        100.000        A        s10        9         9/9      9/9*100

Answer 1

--自己去trim算具体数值 select row_number() over(order by [deviceNo],[type]) as [序号],
deviceNo as [设备编号],
assetNO as [资产编号],
depreciation as [月折旧费],
[type] as [型号],
accessoryNo as [零件号],
sum(lastTime) as [持续时间],
折旧率=rtrim(sum(lastTime))+'/'+rtrim((select sum(lastTime) from tb where t.deviceNo=deviceNo)),
折旧费=rtrim(sum(lastTime))+'/'+rtrim((select sum(lastTime) from tb where t.deviceNo=deviceNo))+'*100'
from tb t,devices 
where deviceNO = selfNO 
group by type,accessoryNO,deviceNo,assetNO,depreciation
/*
序号                   设备编号       资产编号                 月折旧费                                    型号                   零件号                  持续时间        折旧率                       折旧费
-------------------- ---------- -------------------- --------------------------------------- -------------------- -------------------- ----------- ------------------------- -----------------------------
1                    z11        m11                  100.000                                 A                    s10                  19          19/33                     19/33*100
2                    z11        m11                  100.000                                 B                    s10                  14          14/33                     14/33*100
3                    z12        m12                  100.000                                 C                    s11                  5           5/5                       5/5*100
4                    z13        m13                  100.000                                 A                    s10                  9           9/9                       9/9*100(4 行受影响)*/drop table TB,devices

Answer 2

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(5,3)),
折旧费=cast(sum(lastTime)*1.0/(select sum(lastTime) from tb where t.deviceNo=deviceNo) as decimal(5,3) )
from tb t,devices 
where deviceNO = selfNO 
group by type,accessoryNO,deviceNo,assetNO,depreciation