sql 2005将两个查询结果相加 疑难

[volkswageos]

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

if object_id('fees') is not null
drop table fees

create table fees(id int,fee numeric(18, 3))
insert fees
select 1, 100.00

go
--第一个查询 按照将每个设备的折旧按时间分摊到零件号
select 序号=ROW_NUMBER() OVER(ORDER BY a.type,a.accessoryNo), a.type 型别,a.accessoryNo as 零件号,sum(lastTime) 持续时间,SUM(lastTime/sum_lastTime*depreciation) as 设备分配额
from (
select a.type,a.accessoryNo,a.deviceNo,y.depreciation
,lastTime=SUM(a.lastTime)
,sum_lastTime=(SELECT SUM(x.lastTime+0.0) FROM tb AS x WHERE x.deviceNo=a.deviceNo )
from tb as a inner join devices as y on a.deviceNo=y.selfNO
group by a.type,a.accessoryNo,a.deviceNo,y.depreciation
) as a
group by a.type,a.accessoryNo

--第二个查询 将fees表中的固定值fee*该零件lastTime总和/所有零件lastTime
declare @sql1 nvarchar(4000)
declare @count int
declare @sql nvarchar(4000)

set @sql1=N'select @a=sum(lastTime) from tb'
exec sp_executesql @sql1,N'@a int output',@count output
set @sql='select row_number() over(order by [type]) as [序号],[type] as [型号],accessoryNo as [零件号],sum(lastTime) as [持续时间],1.0*sum(lastTime)/'+cast(@count as varchar(10))+'*fee as [管理分配额
]'
set @sql=@sql+' from tb,fees where id = 1 group by type,accessoryNO,fee'
set @sql=@sql+' union all select '''' as 序号,''全部型别'' as [型号] ,''全部零件'' as [零件号],sum(lastTime) as [持续时间],1.0*sum(lastTime)/'+cast(@count as varchar(10))+'*fee as [管理分配额] from tb,fees where id = 1 group by fee'
exec(@sql)


drop table tb
drop table devices
drop table fees


--第一个查询结果

序号 型别 零件号 持续时间 设备分配额
-------------------- -------------------- -------------------- ----------- ---------------------------------------
1 A s10 28 38.78787878788
2 B s10 14 21.21212121212
3 C s11 5 20.00000000000

(3 行受影响)

--第二个查询结果

序号 型号 零件号 持续时间 管理分配额
-------------------- -------------------- -------------------- ----------- ---------------------------------------
1 A s10 28 59.574400000
2 B s10 14 29.787200000
3 C s11 5 10.638200000
0 全部型别 全部零件 47 100.000000000

(4 行受影响)

我的要求是将两个查询结果的后面的分配额按照型别 零件号 累计一下多加一列 总和
即
序号 型别 零件号 持续时间 设备分配额 管理分配额 总和-------------------- -------------------- -------------------- ----------- ---------------------------------------
1 A s10 28 38.78787878788 59.574400000 38.78787878788+59.574400000
2 B s10 14 21.21212121212 29.787200000 21.21212121212+29.787200000
3 C s11 5 20.00000000000 10.638200000 20.00000000000+10.638200000

[volkswageos]

[Quote=引用 11 楼 xys_777 的回复:]
SQL code
--第一个查询 按照将每个设备的折旧按时间分摊到零件号
select 序号=ROW_NUMBER() OVER(ORDER BY a.type,a.accessoryNo), a.type 型别,a.accessoryNo as 零件号,sum(lastTime) 持续时间,SUM(lastTime/sum_lastTime*depreciation) as 设备分配额
i……
[/Quote]

你的第二个查询为什么会插入到#t2中呢？
我自己的就不行啊

[东那个升]

[Quote=引用 13 楼 volkswageos 的回复:]
引用 11 楼 xys_777 的回复:
SQL code
--第一个查询 按照将每个设备的折旧按时间分摊到零件号
select 序号=ROW_NUMBER() OVER(ORDER BY a.type,a.accessoryNo), a.type 型别,a.accessoryNo as 零件号,sum(lastTime) 持续时间,SUM(lastTime/sum_lastTime*dep……
[/Quote]

需要先建立虚拟表。

[永生天地]

眼晕，抢个bd看

[永生天地]

好像是直接的两个字段相加的问题

[东那个升]

把结果存入虚拟表。

2个虚拟表再连接一下就可以了。

[SoftwKLC]

好长啊。。。

[--小F--]

需要两表left join 还有两表两字段相加 建议楼主print出来以后再试 这样直接看着不好理解

[ws_hgo]

好长..........

[volkswageos]

[Quote=引用 4 楼 ldslove 的回复:]
把结果存入虚拟表。

2个虚拟表再连接一下就可以了。
[/Quote]

怎么个存储法啊?

[bancxc]

你可能写复杂了

[htl258_Tony]

if object_id('tb') is not null 
drop table tb
go
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
go
if object_id('devices') is not null 
drop table devices
go
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
if object_id('fees') is not null 
drop table fees
go
create table fees(id int,fee numeric(18, 3))
insert fees 
select 1, 100.00

go
--第一个查询 按照将每个设备的折旧按时间分摊到零件号


--第二个查询 将fees表中的固定值fee*该零件lastTime总和/所有零件lastTime
declare @sql1 nvarchar(4000) 
declare @count int
declare @sql nvarchar(4000) 

set @sql1=N'select @a=sum(lastTime) from tb'
exec sp_executesql @sql1,N'@a int output',@count output
set @sql='select a.*,b.管理分配额,总和=a.设备分配额+isnull(b.管理分配额,0) 
from (
select 序号=ROW_NUMBER() OVER(ORDER BY a.type,a.accessoryNo), a.type 型别,a.accessoryNo as 零件号,sum(lastTime) 持续时间,SUM(lastTime/sum_lastTime*depreciation) as 设备分配额
from (
select a.type,a.accessoryNo,a.deviceNo,y.depreciation
,lastTime=SUM(a.lastTime)
,sum_lastTime=(SELECT SUM(x.lastTime+0.0) FROM tb AS x WHERE x.deviceNo=a.deviceNo ) 
from tb as a inner join devices as y on a.deviceNo=y.selfNO
group by a.type,a.accessoryNo,a.deviceNo,y.depreciation 
) as a
group by a.type,a.accessoryNo)
a left join (select row_number() over(order by [type]) as [序号],[type] as [型号],accessoryNo as [零件号],sum(lastTime) as [持续时间],1.0*sum(lastTime)/'+cast(@count as varchar(10))+'*fee as [管理分配额]'
set @sql=@sql+' from tb,fees where id = 1 group by type,accessoryNO,fee) b on a.序号=b.序号' 
--set @sql=@sql+' union all select '''' as 序号,''全部型别'' as [型号] ,''全部零件'' as [零件号],sum(lastTime) as [持续时间],1.0*sum(lastTime)/'+cast(@count as varchar(10))+'*fee as [管理分配额] from tb,fees where id = 1 group by fee'
exec(@sql)
/*
序号	型别	零件号	持续时间	设备分配额	管理分配额	总和
1	A	s10	28	38.78787878788	59.574400000	98.3622787879
2	B	s10	14	21.21212121212	29.787200000	50.9993212121
3	C	s11	5	20.00000000000	10.638200000	30.6382000000
*/

?

[永生天地]

--第一个查询 按照将每个设备的折旧按时间分摊到零件号
select 序号=ROW_NUMBER() OVER(ORDER BY a.type,a.accessoryNo), a.type 型别,a.accessoryNo as 零件号,sum(lastTime) 持续时间,SUM(lastTime/sum_lastTime*depreciation) as 设备分配额
into #t1
from (
select a.type,a.accessoryNo,a.deviceNo,y.depreciation
,lastTime=SUM(a.lastTime)
,sum_lastTime=(SELECT SUM(x.lastTime+0.0) FROM tb AS x WHERE x.deviceNo=a.deviceNo ) 
from tb as a inner join devices as y on a.deviceNo=y.selfNO
group by a.type,a.accessoryNo,a.deviceNo,y.depreciation 
) as a
group by a.type,a.accessoryNo

create table #t2(
序号 int,
型号 varchar(10),
 零件号 varchar(10),
持续时间 int,
管理分配额 decimal(18,9)
)

--第二个查询 将fees表中的固定值fee*该零件lastTime总和/所有零件lastTime
declare @sql1 nvarchar(4000) 
declare @count int
declare @sql nvarchar(4000) 

set @sql1=N'select @a=sum(lastTime) from tb'
exec sp_executesql @sql1,N'@a int output',@count output
set @sql='insert into #t2 select row_number() over(order by [type]) as [序号],[type] as [型号],accessoryNo as [零件号],sum(lastTime) as [持续时间],1.0*sum(lastTime)/'+cast(@count as varchar(10))+'*fee as [管理分配额
]'
set @sql=@sql+'  from tb,fees where id = 1 group by type,accessoryNO,fee' 
set @sql=@sql+' union all select '''' as 序号,''全部型别'' as [型号] ,''全部零件'' as [零件号],sum(lastTime) as [持续时间],1.0*sum(lastTime)/'+cast(@count as varchar(10))+'*fee as [管理分配额] from tb,fees where id = 1 group by fee'
exec(@sql)


select a.序号 ,a.型别 ,a.零件号 ,a.持续时间,a.设备分配额,b.管理分配额 ,a.设备分配额+b.管理分配额 as 总和
from #t1 a ,#t2 b
where a.序号=b.序号


序号                   型别                   零件号                  持续时间        设备分配额                                    管理分配额                总和                                       
-------------------- -------------------- -------------------- ----------- ---------------------------------------- -------------------- ---------------------------------------- 
1                    A                    s10                  28          38.78787878788                           59.574400000         98.36227878788
2                    B                    s10                  14          21.21212121212                           29.787200000         50.99932121212
3                    C                    s11                  5           20.00000000000                           10.638200000         30.63820000000

（所影响的行数为 3 行）

[ChinaJiaBing]

试用存储过程...........