** 原代码： **
** /* ** ** 福建公安高等专科学校当月无补助有前期补助的卡实现在窗机上领取 ** ** 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
|
密码
|
<span lang="EN-US" style="FONT