原代码：
/*福建公安高等专科学校当月无补助有前期补助的卡实现在窗机上领取T-SQL代码（当月补助形成后发放前处理的）*/
declare @month/*月份*/ datetime,@k /*卡类别*/ int,@customerid int /*客户号*/, @subsidyfare/*补助额*/ money
select top 1 @month= month from t_subsidymonth order by month desc/*获取补助当月份值*/
declare k cursor for select cardtype from t_subsidymonth where plancount=0 and month=@month
open k
fetch next from k into @k
while (@@fetch_status=0)
begin
declare custid_dist cursor for select distinct customerid from t_subsidynotputout where cardtype=@k
open custid_dist
fetch next from custid_dist into @customerid
while (@@fetch_status=0)
begin
insert into t_subsidymonthplan(month,customerid,cardtype,subsidy) values(@month,@customerid,@k,0) –关键
fetch next from custid_dist into @customerid
end
close custid_dist
deallocate custid_dist
declare custid_subsidyfare cursor for select customerid,sum(subsidy) as sum_subsidy from t_subsidynotputout where cardtype=@k group by customerid
open custid_subsidyfare
fetch next from custid_subsidyfare into @customerid,@subsidyfare
while (@@fetch_status=0)
begin
update t_customers set subsidydt=@month ,cursubsidyfare=@subsidyfare,subsidyout='F' where customerid =@customerid –关键
fetch next from custid_subsidyfare into @customerid,@subsidyfare
end
close custid_subsidyfare
deallocate custid_subsidyfare
fetch next from k into @k
end
close k
deallocate k
优化后代码：
declare @month/*月份*/ datetime
select top 1 @month= month from t_subsidymonth order by month desc/*获取补助当月份值*/
update t_customers set t_customers.subsidydt=@month ,t_customers.cursubsidyfare=l.sum_subsidy ,t_customers.subsidyout='F' from ( select customerid,sum(subsidy) as sum_subsidy from t_subsidynotputout where cardtype in (select cardtype from t_subsidymonth where plancount=0 and month=@month) group by customerid) as l
where t_customers.customerid=l.customerid
insert into t_subsidymonthplan(month,customerid,cardtype,subsidy) select subsidydt,customerid,cardtype,0 as subsidyfare from t_customers where customerid in (select distinct customerid from t_subsidynotputout where cardtype in (select cardtype from t_subsidymonth where plancount=0 and month=@month))
附：用到的基本表信息
表名：T_Customers（客户信息）
字段名
类型
意义
是否主键
[CustomerID]
[int] NOT NULL
客户代码
Y
[StatCode]
[varchar] (3) NOT NULL
工作站代码
[Account]
[varchar] (7) NOT NULL
单位代码
[Name]
[varchar] (12) NOT NULL
姓名
[CardNo]
[int] NOT NULL
卡号
[CardType]
[tinyint] NOT NULL
卡类别
[Status]
[tinyint] NOT NULL
状态
[OpenDt]
[datetime] NOT NULL
操作日期
[CashID]
[smallint] NOT NULL
出纳员代码
[SumFare]
[smallmoney] NOT NULL
总额
[ConsumeFare]
[smallmoney] NOT NULL
消费额
[OddFare]
[smallmoney] NOT NULL
余额
[OpCount]
[int] NOT NULL
操作次数
[CurSubsidyFare]
[smallmoney] NOT NULL
本月补助费
*
[SubsidyDT]
[datetime] NOT NULL
补助日期
*
[SubsidyOut]
[char] (1) NOT NULL
发放标记
*
[Alias]
[varchar] (10) NULL
别名
[outid]
[varchar] (16) NULL
工号
[UpdateID]
[tinyint] NOT NULL
是否更新标记
[Pwd]
[char] (4) NULL
密码
[QuChargFare]
[smallmoney] NULL
申请转帐金额
[HasTaken]
[tinyint] NULL
是否领取
[DragonCardNo]
[char] (19) NULL
龙卡卡号
[ApplyCharg]
[smallmoney] NULL
领款金额
[ChargPer]
[smallmoney] NULL
转帐标准
[MingZu]
[varchar] (20) NULL
民族
[Sex]
[char] (2) NULL
性别
[Memo]
[varchar] (100) NULL
备注
[WeiPeiDW]
[varchar] (10) NULL
委培单位
[CardConsumeType]
[tinyint] NULL
卡消费类型
[LeaveSchoolDT]
[datetime] NULL
离校日期
表名：T_SubsidyMonth（补助月合计）
字段名
类型
意义
是否主键
[Month]
[datetime] NOT NULL ,
补助月
Y
[CardType]
[tinyint] NOT NULL ,
卡类型
Y
[PlanCount]
[int] NOT NULL ,
计划发放次数
N
[PlanSubsidy]
[money] NOT NULL ,
计划补助总额
N
[PutOutCount]
[int] NOT NULL ,
已发补助次数
N
[PutOutSubsidy]
[money] NOT NULL ,
已发补助金额
N
[LastCount]
[int] NOT NULL ,
剩余次数
N
[LastSubsidy]
[money] NOT NULL
剩余补助
N
[PlanPre]
[money] NOT NULL
前期未发
N
[PlanPreCount]
[int] NOT NULL ,
前期未发次数
N
[PutOutPre]
[money] NOT NULL ,
前期已发金额
N
[PutOutPreCount]
[int] NOT NULL ,
前期已发次数
N
[LastPre]
[money] NOT NULL ,
前期剩余金额
N
[LastPreCount]
[int] NOT NULL ,
前期剩余次数
N
[HasCloseOff]
[bit] NOT NULL ,
是否月结
N
[CloseOffDt]
[datetime] NOT NULL ,
月结日期
N
[Opdt]
[datetime] NOT NULL ,
操作日期
N
[ManegerID]
[tinyint] NOT NULL
管理员ID
N
表名：T_SubsidyMonthPlan（补助月计划）
字段名
类型
意义
是否主键
[Month]
[datetime] NOT NULL ,
补助月
N
[CustomerID]
[int] NOT NULL ,
客户ID
Y
[CardType]
[tinyint] NOT NULL ,
卡类型
N
[Subsidy]
[smallmoney] NOT NULL
补助额
N
表名：T_SubsidyNotPutOut（前期未发补助）
字段名
类型
意义
是否主键
[Month]
[datetime] NOT NULL ,
补助月
Y
[CustomerID]
[int] NOT NULL
客户ID
Y
[CardType]
[tinyint] NOT NULL ,
卡类型
N
[Subsidy]
[smallmoney] NOT NULL
补助额
N
