declare @BillNo varchar(20)
declare @xh int
declare @OverLevel int --已完成的审批次数
declare @SumLevel int --总共的审批次数
declare @CheckUser varchar(254)
declare my_cur cursor for
select BillNo,xh,SumLevel from #tmp_bill
open my_cur
fetch next from my_cur into @BillNo, @Xh, @SumLevel
while @@fetch_status=0
begin
select @OverLevel=count(1) from CheckRec where Bill='jh' and BillNo=@BillNo and Xh=@xh --计算已经完成的审批次数
select @CheckUser=AppUser from work_flow where Bill='jh' and pLevel=@SumLevel-@OverLevel --当前级别的审核人
update #tmp_bill set OverLevel=@OverLevel, CheckUser=@CheckUser WHERE CURRENT OF my_cur
fetch next from my_cur into @BillNo, @Xh, @SumLevel
end
close my_cur
deallocate my_cur从表CheckRec中(根据#tmp_bill的BillNo和Xh)取记录条数更新到#tmp_bill表中的OverLevel
从表work_flow中(#tmp_bill的SumLevel和上一步计算出来的OverLevel相减的结果)查找当前级别的审核人,然后更新到#tmp_bill的CheckUser表#tmp_bill 记录数5000多,表CheckRec记录数5000多,work_flow记录数50多条,
以上游标计算非常慢,想用update语句替换游标,请高手指教。
注:临时表#tmp_bill的BillNo和Xh已经建立索引。
declare @xh int
declare @OverLevel int --已完成的审批次数
declare @SumLevel int --总共的审批次数
declare @CheckUser varchar(254)
declare my_cur cursor for
select BillNo,xh,SumLevel from #tmp_bill
open my_cur
fetch next from my_cur into @BillNo, @Xh, @SumLevel
while @@fetch_status=0
begin
select @OverLevel=count(1) from CheckRec where Bill='jh' and BillNo=@BillNo and Xh=@xh --计算已经完成的审批次数
select @CheckUser=AppUser from work_flow where Bill='jh' and pLevel=@SumLevel-@OverLevel --当前级别的审核人
update #tmp_bill set OverLevel=@OverLevel, CheckUser=@CheckUser WHERE CURRENT OF my_cur
fetch next from my_cur into @BillNo, @Xh, @SumLevel
end
close my_cur
deallocate my_cur从表CheckRec中(根据#tmp_bill的BillNo和Xh)取记录条数更新到#tmp_bill表中的OverLevel
从表work_flow中(#tmp_bill的SumLevel和上一步计算出来的OverLevel相减的结果)查找当前级别的审核人,然后更新到#tmp_bill的CheckUser表#tmp_bill 记录数5000多,表CheckRec记录数5000多,work_flow记录数50多条,
以上游标计算非常慢,想用update语句替换游标,请高手指教。
注:临时表#tmp_bill的BillNo和Xh已经建立索引。
declare @BillNo varchar(20)
declare @xh int
declare @OverLevel int --已完成的审批次数
declare @SumLevel int --总共的审批次数
declare @CheckUser varchar(254)
declare my_cur cursor for
select BillNo,xh,SumLevel from #tmp_bill
open my_cur
fetch next from my_cur into @BillNo, @Xh, @SumLevel
while @@fetch_status=0
begin
select @OverLevel=count(1) from CheckRec where Bill='jh' and BillNo=@BillNo and Xh=@xh --计算已经完成的审批次数
select @CheckUser=AppUser from work_flow where Bill='jh' and pLevel=@SumLevel-@OverLevel --当前级别的审核人
update #tmp_bill set OverLevel=@OverLevel, CheckUser=@CheckUser WHERE CURRENT OF my_cur
fetch next from my_cur into @BillNo, @Xh, @SumLevel
end
close my_cur
deallocate my_cur
为了方便查看,贴一下代码格式。
OverLevel=(select count(1) from #tmp_bill where BillNo=t.BillNo and Xh=t.xh),
CheckUser=(select max(AppUser) from work_flow where pLevel=t.SumLevel-t.OverLevel)
from #tmp_bill t
where bill='jh'
--try
update t
set OverLevel=isnull((select count(1) from CheckRec where Bill='jh' and BillNo=t.BillNo and Xh=t.xh),0),
CheckUser=(select AppUser from work_flow where Bill='jh' and
pLevel= SumLevel - (isnull(select count(1) from CheckRec where Bill='jh'
and BillNo=t.BillNo and Xh=t.xh ),0))
from #tmp_bill t
update t
set OverLevel=isnull((select count(1) from CheckRec where Bill='jh' and BillNo=t.BillNo and Xh=t.xh),0),
CheckUser=(select AppUser from work_flow where Bill='jh' and
pLevel= SumLevel - (isnull(select count(1) from CheckRec where Bill='jh'
and BillNo=t.BillNo and Xh=t.xh ),0))
from #tmp_bill t