我已经找到方法select t1.curYear,t1.curMonth,sum(t2.num1) as sum1,sum(t2.num2) as sum2 into ##tmp from
t1 inner join t2 on t1.curYear = t2.curYear and t1.curMonth = t2.curMonth
group by t1.curYear,t1.curMonth
order by t1.curYear,t1.curMonthdeclare @sum1 int
declare @sum2 int
declare @curYear int
set @sum1 = 0
set @sum2= 0
set @curYear = -1update ##tmp set @sum1 = case when @curYear = curYear then sum1+ @sum1 else sum1 end,
sum1 = @sum1,
@sum2 = case when @curYear = curYear then sum2+ @sum2 else sum2 end,
sum2 = @sum2,
@curYear = curYearupdate t1 set sum1 = t.sum1 ,sum2 = t.sum2 From t1 inner join ##tmp t
on t1.curYear = t.curYear and t1.curMonth = t.curMonthselect * from ##tmp
--drop table ##tmp
t1 inner join t2 on t1.curYear = t2.curYear and t1.curMonth = t2.curMonth
group by t1.curYear,t1.curMonth
order by t1.curYear,t1.curMonthdeclare @sum1 int
declare @sum2 int
declare @curYear int
set @sum1 = 0
set @sum2= 0
set @curYear = -1update ##tmp set @sum1 = case when @curYear = curYear then sum1+ @sum1 else sum1 end,
sum1 = @sum1,
@sum2 = case when @curYear = curYear then sum2+ @sum2 else sum2 end,
sum2 = @sum2,
@curYear = curYearupdate t1 set sum1 = t.sum1 ,sum2 = t.sum2 From t1 inner join ##tmp t
on t1.curYear = t.curYear and t1.curMonth = t.curMonthselect * from ##tmp
--drop table ##tmp
-----------------------------------------------------------------
select 2003 as curYear, 01 as curMonth, 0 as sum1, 0 as sum2 ,GETDATE( ) as Firstday,GETDATE( ) as MonLastDay
into t1 delete t1insert t1 values(2004,1,0,0,'2004-01-01','2004-1-31')
insert t1 values(2004,2,0,0,'2004-01-01','2004-2-29')
insert t1 values(2004,3,0,0,'2004-01-01','2004-3-31')
insert t1 values(2005,1,0,0,'2005-01-01','2005-1-31')
insert t1 values(2005,2,0,0,'2005-01-01','2005-2-28')
insert t1 values(2005,3,0,0,'2005-01-01','2005-3-31')select * from t1
-- drop table t1select 2003 as curYear ,01 as curMonth ,GETDATE() as curDate,1 as num1, 2 as num2
into t2delete t2insert t2 values(2004,1,'2004-1-3',1,3)
insert t2 values(2004,1,'2004-1-4',5,7)
insert t2 values(2004,2,'2004-2-3',11,13)
insert t2 values(2004,2,'2004-2-5',17,19)
insert t2 values(2004,3,'2004-3-3',23,29)
insert t2 values(2004,3,'2004-3-8',31,37)
insert t2 values(2005,1,'2005-1-3',41,43)
insert t2 values(2005,1,'2005-1-8',47,51)
insert t2 values(2005,2,'2005-2-3',53,57)
insert t2 values(2005,2,'2005-2-9',59,61)
insert t2 values(2005,3,'2005-3-3',53,57)
insert t2 values(2005,3,'2005-3-9',59,61)select * from t2-- drop table t2-- 方法1
update t1 set sum1 = (select sum(num1) from t2 where curDate>= t1.firstDay and curDate<= t1.monLastday),
sum2 = (select sum(num2) from t2 where curDate>= t1.firstDay and curDate<= t1.monLastday)
/* t1:
curYear curMonth sum1 sum2 Firstday MonLastDay
----------- ----------- ----------- ----------- ------------------------------------------------------ ------------------------------------------------------
2004 1 6 10 2004-01-01 00:00:00.000 2004-01-31 00:00:00.000
2004 2 34 42 2004-01-01 00:00:00.000 2004-02-29 00:00:00.000
2004 3 88 108 2004-01-01 00:00:00.000 2004-03-31 00:00:00.000
2005 1 88 94 2005-01-01 00:00:00.000 2005-01-31 00:00:00.000
2005 2 200 212 2005-01-01 00:00:00.000 2005-02-28 00:00:00.000
2005 3 312 330 2005-01-01 00:00:00.000 2005-03-31 00:00:00.000（所影响的行数为 6 行）
*/ /*
curYear curMonth curDate num1 num2
----------- ----------- ------------------------------------------------------ ----------- -----------
2004 1 2004-01-03 00:00:00.000 1 3
2004 1 2004-01-04 00:00:00.000 5 7
2004 2 2004-02-03 00:00:00.000 11 13
2004 2 2004-02-05 00:00:00.000 17 19
2004 3 2004-03-03 00:00:00.000 23 29
2004 3 2004-03-08 00:00:00.000 31 37
2005 1 2005-01-03 00:00:00.000 41 43
2005 1 2005-01-08 00:00:00.000 47 51
2005 2 2005-02-03 00:00:00.000 53 57
2005 2 2005-02-09 00:00:00.000 59 61
2005 3 2005-03-03 00:00:00.000 53 57
2005 3 2005-03-09 00:00:00.000 59 61（所影响的行数为 12 行）
*/