sql 游标 用不明白了

Question

现在有三个表 ghy_Jxc_InventoryMx，ghy_Jxc_GoodsSet，ghy_Jxc_OutInventoryMghy_Jxc_InventoryMx字段有：GoodsId int,Amount int,XsDj decimal,JhDj decimal,
ghy_Jxc_GoodsSet字段有：GoodsId int，GoodsMc varchar(50)
ghy_Jxc_OutInventoryM字段有：GoodsId int ,cou int
现在我想生成一个表A 字段为GoodsMc，JhDj ，XsDj ,Amount ,cou 
应该在么做，我现在在存储过程中将创建临时表A 用游标将数据插入，我游标用的不是很熟，LTER PROCEDURE [dbo].[selectGoodsToTable]
as 
begin 
declare @Idcount int,@GoodsId int,@GoodName varchar(50),@JhJg Decimal,@JhSl int,@XsJg Decimal,@XsSl int 
select @GoodsId=GoodsId from ghy_Jxc_GoodsSet
select @Idcount=count(*) from ghy_Jxc_GoodsSet
create table #tmp(GoodsId int,GoodName varchar(50),JhJg Decimal,JhSl int,XsJg Decimal,XsSl int)
--定义游标 
while(@Idcount>0)
begin
declare _tallybyrdType cursor read_only 
for 
SELECT  MAX(ghy_Jxc_InventoryMx.GjDj) as JhJg, SUM(ghy_Jxc_InventoryMx.BoxCount) as JhSl, MAX(ghy_Jxc_InventoryMx.XsDj) as XsJg, 
                      ghy_Jxc_GoodsSet.GoodsMc as GoodName, ghy_Jxc_InventoryMx.GoodsId as GoodsId
FROM         ghy_Jxc_InventoryMx INNER JOIN ghy_Jxc_GoodsSet ON ghy_Jxc_InventoryMx.GoodsId = ghy_Jxc_GoodsSet.GoodsId
where ghy_Jxc_InventoryMx.GoodsId=@GoodsId
GROUP BY ghy_Jxc_InventoryMx.GoodsId, ghy_Jxc_GoodsSet.GoodsMc--SELECT     SUM(Amount) AS XsSl
--FROM         ghy_Jxc_OutInventoryM
--GROUP BY GoodsIdopen JhSl 
fetch next from _tallybyrdType into @JhJg,@JhSl,@XsJg,@GoodName,@GoodsId--@XsSl
while(@@fetch_status=0) 
begin 
if(@XsSl=null)
begin
insert into #tmp(GoodsId,GoodName,JhJg,JhSl,XsJg,XsSl)values(@GoodsId,@GoodName,@JhJg,@JhSl,@XsJg,0)
end
else
begin
insert into #tmp(GoodsId,GoodName,JhJg,JhSl,XsJg,XsSl)values(@GoodsId,@GoodName,@JhJg,@JhSl,@XsJg,0)
end
fetch next from _tallybyrdType into @JhJg,@JhSl,@XsJg,@GoodName,@XsSl
end 
close _tallybyrdType 
deallocate _tallybyrdType 
select @Idcount=@Idcount-1
endselect * from #tmp
drop table #tmpend
我想一个GoodsId为一行在A中显示，应该怎么插入，或者有没有别的什么方法实现？

Answer 1

--创建ghy_Jxcc_InventoryMx表
create table ghy_Jxcc_InventoryMx
(
GoodsId int,
Amount int,
XsDj decimal,
JhDj decimal
)
--插入数据
insert into ghy_Jxcc_InventoryMx values(1,10,100,100)
insert into ghy_Jxcc_InventoryMx values(2,20,200,200)
insert into ghy_Jxcc_InventoryMx values(3,30,300,300)
insert into ghy_Jxcc_InventoryMx values(4,40,400,400)
insert into ghy_Jxcc_InventoryMx values(5,50,500,500)
select * from ghy_Jxcc_InventoryMx
------------------------------------------------
--创建ghy_Jxc_GoodsSet表
create table ghy_Jxc_GoodsSet
(
GoodsId int,
GoodsMc varchar(50)
)
--插入数据
insert into ghy_Jxc_GoodsSet values(1,'牙膏')
insert into ghy_Jxc_GoodsSet values(2,'牙刷')
insert into ghy_Jxc_GoodsSet values(3,'杯子')
insert into ghy_Jxc_GoodsSet values(4,'凳子')
insert into ghy_Jxc_GoodsSet values(5,'椅子')
select * from ghy_Jxc_GoodsSet
------------------------------------------
--创建ghy_Jxc_OutInventory表
create table ghy_Jxc_OutInventory
(
GoodsId int,
cou int
)
--插入数据
insert into ghy_Jxc_OutInventory values(1,11)
insert into ghy_Jxc_OutInventory values(2,22)
insert into ghy_Jxc_OutInventory values(3,33)
insert into ghy_Jxc_OutInventory values(4,44)
insert into ghy_Jxc_OutInventory values(5,55)
select * from ghy_Jxc_OutInventory
--------------------------------------------
--创建存储过程
create proc test
as
--存储过程中声明零时表A
declare @A table(GoodsMc varchar(50),JhDj decimal,XsDj decimal,Amount int,cou int)declare @GoodsMc varchar(50)
declare @JhDj decimal
declare @XsDj decimal
declare @Amount int
declare @cou int
--在存储过程中建游标
declare cur_test cursor for
 select g2.GoodsMc,g1.JhDj,g1.XsDj,g1.Amount,g3.cou
 from ghy_Jxcc_InventoryMx as g1,ghy_Jxc_GoodsSet as g2,ghy_Jxc_OutInventory as g3
 where g1.GoodsId=g2.GoodsId and g2.GoodsId=g3.GoodsId
open cur_test
fetch Next from cur_test into @GoodsMc,@JhDj,@XsDj,@Amount,@cou
while(@@fetch_status=0)
 begin
insert into @A values(@GoodsMc,@JhDj,@XsDj,@Amount,@cou)
fetch Next from Cur_test into @GoodsMc,@JhDj,@XsDj,@Amount,@cou
 end
close cur_test
deallocate cur_test
select * from @A
goexec test我试过了没错 如果没理解错你的题意的话