数据库简单应用

小弟刚接触SQL，现有一问题求前辈解决，如下：
由于不能直接添加图片（感觉这个CSDN是不是要改下，添加图片一点都不好），我就手动下。
-----------------------------------------------------------------------------------------------------
商品ID 商品PersonCode 商品FullName 仓库ID 仓库PersonCode 仓库FullName 库存数量 库存金额
00001 00001 乒乓球 00001 sdf sdfsdf 15.000000 24.000000
00001 00001 乒乓球 00001 sdf sdfsdf 5.000000 12.500000
00001 00001 乒乓球 00002 sdf1 sss 1.000000 2.500000
------------------------------------------------------------------------------------------------------
现在我要实现的是：
-----------------------------------------------------------------------------------------------------
商品ID 商品PersonCode 商品FullName 仓库ID 仓库PersonCode 仓库FullName 库存数量 库存金额
00001 00001 乒乓球 00001 sdf sdfsdf 20.000000 36.500000
00001 00001 乒乓球 00002 sdf1 sss 1.000000 2.500000
------------------------------------------------------------------------------------------------------

就是第一条与第二条记录合并了（最后两列为和）

下面是我的SQL语句（第一种情况的）：
----------------------------------------------------------------
if object_id('test') is not null
drop procedure test
go
create procedure test --创建存储过程
@ID varchar(50) --输入
as
select '商品ID' = D_Goods.Id, '商品PersonCode' = D_Goods.PersonCode, '商品FullName' = D_Goods.FullName, 
  '仓库ID' = d_Stock.ID, '仓库PersonCode' = d_Stock.PerSonCode, '仓库FullName' = d_Stock.FullName,
  '库存数量' = d_StockGoods.Num, '库存金额' = d_StockGoods.Amount
from d_StockGoods, d_Stock, d_Goods
 
where d_Stock.ID = d_StockGoods.sID and d_StockGoods.gID = d_Goods.ID and d_Goods.ID = @ID 
go

execute test '00001'
-------------------------------------------

谢了，前辈们。

SQL code

select 商品ID,商品PersonCode,商品FullName,仓库ID,仓库PersonCode,仓库FullName,
       库存数量=sum(库存数量),库存金额=sum(库存金额)
from tb group by 商品ID,商品PersonCode,商品FullName,仓库ID,仓库PersonCode,仓库FullName

SQL code

create procedure test    --创建存储过程
@ID varchar(50)    --输入
as
select 商品ID,商品PersonCode,商品FullName,仓库ID,仓库PersonCode,仓库FullNamem,SUM(库存数量)库存数量,SUM(库存金额)库存金额
from(
select '商品ID' = D_Goods.Id, '商品PersonCode' = D_Goods.PersonCode, '商品FullName' = D_Goods.FullName,  
  '仓库ID' = d_Stock.ID, '仓库PersonCode' = d_Stock.PerSonCode, '仓库FullName' = d_Stock.FullName,
  '库存数量' = d_StockGoods.Num, '库存金额' = d_StockGoods.Amount
from d_StockGoods, d_Stock, d_Goods
where d_Stock.ID = d_StockGoods.sID and d_StockGoods.gID = d_Goods.ID and d_Goods.ID = @ID 
)t group by 商品ID,商品PersonCode,商品FullName,仓库ID,仓库PersonCode,仓库FullNamem
go    

或者:
SQL code

create procedure test    --创建存储过程
@ID varchar(50)    --输入
as

select '商品ID' = D_Goods.Id, '商品PersonCode' = D_Goods.PersonCode, '商品FullName' = D_Goods.FullName,  
  '仓库ID' = d_Stock.ID, '仓库PersonCode' = d_Stock.PerSonCode, '仓库FullName' = d_Stock.FullName,
  '库存数量' = sum(d_StockGoods.Num), '库存金额' = sum(d_StockGoods.Amount)
from d_StockGoods, d_Stock, d_Goods
where d_Stock.ID = d_StockGoods.sID and d_StockGoods.gID = d_Goods.ID and d_Goods.ID = @ID 
group by D_Goods.Id,D_Goods.PersonCode,D_Goods.FullName,d_Stock.ID,d_Stock.PerSonCode,d_Stock.FullName

go    

SQL code

create table kyle_huang
(商品ID varchar(10),
 商品PersonCode varchar(10),
 商品FullName varchar(10),
 仓库ID varchar(10),
 仓库PersonCode varchar(10),
 仓库FullName varchar(10),
 库存数量 numeric(10,6),
 库存金额 numeric(10,6)
)

insert into kyle_huang
select '00001','00001','乒乓球','00001','sdf','sdfsdf','15.000000','24.000000' union all
select '00001','00001','乒乓球','00001','sdf','sdfsdf','5.000000','12.500000' union all
select '00001','00001','乒乓球','00002','sdf1','sss','1.000000','2.500000'


select 商品ID, 商品PersonCode, 商品FullName, 仓库ID, 仓库PersonCode, 仓库FullName,
sum(库存数量) '库存数量', sum(库存金额) '库存金额'
from kyle_huang
group by 
商品ID, 商品PersonCode, 商品FullName, 仓库ID, 仓库PersonCode, 仓库FullName


商品ID      商品PersonCode 商品FullName 仓库ID  仓库PersonCode 仓库FullName 库存数量      库存金额
---------- ------------ ---------- ---------- ------------ ---------- --------------- -----------
00001      00001        乒乓球        00001      sdf          sdfsdf     20.000000      36.500000
00001      00001        乒乓球        00002      sdf1         sss        1.000000       2.500000

(2 row(s) affected)