对T-SQL代码进行优化的一个例子（完全抛弃游标,行数减少了十部）
原代码：
/*福建公安高等专科学校当月无补助有前期补助的卡实现在窗机上领取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