set @summoney=0 set @temp_money=0 set @sec_summoney=0
declare aaa cursor for select id from huiyuan open aaa fetch next from aaa into @temp_id while @@fetch_status=0 begin
declare bbb cursor for select aid FROM huiyuan_temp where huiyuan_id like '%'+@temp_id+'%' open bbb fetch next from bbb into @temp_id_1 while @@FETCH_STATUS = 0 begin
select @temp_money=sum(a001)+sum(b001)+sum(c001)+sum(d001)+sum(e001)+sum(f001)+sum(g001) from allshuju where shujuid=@temp_id_1 and flag=0 ------------------------------------------------------ select @sec_summoney=@sec_summoney+@temp_money ------------------------------------------------------ fetch next from bbb into @temp_id_1
END CLOSE bbb DEALLOCATE bbb
------------------------------------------------------ set @summoney=@summoney+@sec_summoney ------------------------------------------------------ select @xiafoei_money=@summoney*@canshu exec z_write @temp_id,'a',0,'b',@xiafoei_money
FETCH NEXT FROM aaa into @temp_id
END CLOSE aaa DEALLOCATE aaa -----------------------
完全可以通过表联接的方式来做,最多通过几张临时表。 游标其实是不符合SQL理念的,SQL是基于集合的,而游标还是面向过程的编写概念的。 一般而言,游标都可以通过表联接来处理。 因为没有你具体的表结构和数据,只能大致给点思路:;WITH AAA AS ( SELECT id,a001,b001,c001,d001,e001,f001,g001 FROM huiyuan AS A WITH(NOLOCK) INNER JOIN huiyuan_temp AS B WITH(NOLOCK) ON B.huiyuan_id LIKE '%'+A.id+'%' INNER JOIN allshuju AS C WITH(NOLOCK) ON B.aid=C.shujuid ) ,BBB AS ( SELECT id, (sum(a001)+sum(b001)+sum(c001)+sum(d001)+sum(e001)+sum(f001)+sum(g001))*@canshu AS heji FROM AAA GROUP BY id ) INSERT INTO table1 SELECT id,'a',0,'b',heji FROM BBB --以上代码适合MSSQL2005及以上版本,另外原来的exec z_write修改成直接使用INSERT INTO语句
select @summoney=0
select @temp_money=0也就是说 外层 游标循环的
while @@fetch_status=0
begin select @summoney=0
select @temp_money=0这里有问题
select @temp_money=sum(a001)+sum(b001)+sum(c001)+sum(d001)+sum(e001)+sum(f001)+sum(g001)
改成
select @temp_money=isnull(sum(a001),0)+isnull(sum(b001),0)
+isnull(sum(c001),0)+isnull(sum(d001),0)+isnull(sum(e001),0)
+isnull(sum(f001),0)+isnull(sum(g001),0)看看呢
见代码
declare @summoney money,@temp_money money,@sec_summoney money,@temp_id int
set @summoney=0
set @temp_money=0
set @sec_summoney=0
declare aaa cursor for
select id from huiyuan
open aaa
fetch next from aaa into @temp_id
while @@fetch_status=0
begin
declare bbb cursor for
select aid FROM huiyuan_temp where huiyuan_id like '%'+@temp_id+'%'
open bbb
fetch next from bbb into @temp_id_1
while @@FETCH_STATUS = 0
begin
select @temp_money=sum(a001)+sum(b001)+sum(c001)+sum(d001)+sum(e001)+sum(f001)+sum(g001) from allshuju where shujuid=@temp_id_1 and flag=0
------------------------------------------------------
select @sec_summoney=@sec_summoney+@temp_money
------------------------------------------------------
fetch next from bbb into @temp_id_1
END CLOSE bbb
DEALLOCATE bbb
------------------------------------------------------
set @summoney=@summoney+@sec_summoney
------------------------------------------------------
select @xiafoei_money=@summoney*@canshu exec z_write @temp_id,'a',0,'b',@xiafoei_money
FETCH NEXT FROM aaa into @temp_id
END
CLOSE aaa
DEALLOCATE aaa
-----------------------
游标其实是不符合SQL理念的,SQL是基于集合的,而游标还是面向过程的编写概念的。
一般而言,游标都可以通过表联接来处理。
因为没有你具体的表结构和数据,只能大致给点思路:;WITH AAA AS
(
SELECT id,a001,b001,c001,d001,e001,f001,g001
FROM huiyuan AS A WITH(NOLOCK) INNER JOIN
huiyuan_temp AS B WITH(NOLOCK) ON B.huiyuan_id LIKE '%'+A.id+'%' INNER JOIN
allshuju AS C WITH(NOLOCK) ON B.aid=C.shujuid
)
,BBB AS
(
SELECT id,
(sum(a001)+sum(b001)+sum(c001)+sum(d001)+sum(e001)+sum(f001)+sum(g001))*@canshu AS heji
FROM AAA
GROUP BY id
)
INSERT INTO table1
SELECT id,'a',0,'b',heji FROM BBB
--以上代码适合MSSQL2005及以上版本,另外原来的exec z_write修改成直接使用INSERT INTO语句
表A(huiyuan)中有id,表B(huiyuan_temp)中有aid,表c(allshuju)
通过表A的id找到对应的表B的cid,再通过cid去找到对应表c中的shujuid,去求和c表中的
sum(a001)+sum(b001)+sum(c001)+sum(d001)+sum(e001)+sum(f001)+sum(g001)代码就不写了哈