Ô­´úÂ룺

/*¸£½¨¹«°²¸ßµÈר¿ÆѧУµ±ÔÂÎÞ²¹ÖúÓÐÇ°ÆÚ²¹ÖúµÄ¿¨ÊµÏÖÔÚ´°»úÉÏÁìÈ¡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))

¸½£ºÓõ½µÄ»ù±¾±íÐÅÏ¢