select 
    a.Year,
    b.Month,
    Amount=isnull(c.Amount,0),
    YTD   =isnull(c.YTD,(select top 1 YTD from test where Year=a.Year and Month<b.Month order by Month desc))
from 
    (select distinct year  from test) a
cross join
    (select distinct Month from test) b
left join
    test c
on
    a.Year=c.Year and b.Month=c.Month

------解决方案--------------------
仅仅针对以上数据吗,还是说表中没有哪个月份的记录,我在查询时就自动给补上该月份,

------解决方案--------------------

SQL code

--可能在原來就處理，更好一些，如果對結果處理
create table #t([year] int,[month] int,amount int,ytd int)
insert into #t select 2006,1,200,200 
insert into #t select 2006,2,300,500 
insert into #t select 2006,3,200,700 
insert into #t select 2006,4,100,800 
insert into #t select 2007,1,50,50 
insert into #t select 2007,2,10,60 

select A.[year],A.[month],isnull(B.amount,0) as amount,
isnull(B.ytd,(select max(ytd) from #t where [year]=A.[year])) as ytd
from 
( select distinct t1.[year],t2.[month]  from 
   ( select distinct [month] from #t ) t2
       left join #t t1 on 1=1 ) A 
left join #t B
on A.[year]=B.[year] and A.[month]=B.[month]
order by A.[year],A.[month]
/*
year        month       amount      ytd         
----------- ----------- ----------- ----------- 
2006        1           200         200
2006        2           300         500
2006        3           200         700
2006        4           100         800
2007        1           50          50
2007        2           10          60
2007        3           0           60
2007        4           0           60
*/
drop table #t

------解决方案--------------------
就你目前的说法,必须使用临时表,可以建立一个,也可以象那样在查询的时候生成一个.

------解决方案--------------------
学习~~

------解决方案--------------------
SQL code
declare @test table(Year int,Month int,Amount int,YTD int)
insert into @test values(2006,1,200,200) 
insert into @test values(2006,2,300,500) 
insert into @test values(2006,3,200,700) 
insert into @test values(2006,4,100,800) 
insert into @test values(2007,1,50 ,50 )
insert into @test values(2007,2,10 ,60 )

declare @Months table(Month int)
declare @maxMonth int, @i int
select @maxMonth=9, @i=1
while @i<=@maxMonth
begin
    insert into @Months values(@i)
    set @i=@i+1
end

select a.Year, b.Month,
    Amount=isnull(c.Amount,0),
    YTD   =isnull(c.YTD,(select top 1 YTD from @test where Year=a.Year and Month<b.Month order by Month desc))
from (select distinct year  from @test) a
    cross join @Months b
    left join @test c on a.Year=c.Year and b.Month=c.Month
union all
select * from @test where Month>@maxMonth

/*
       Year       Month      Amount         YTD
----------- ----------- ----------- -----------
       2006           1         200         200
       2006           2         300         500
       2006           3         200         700
       2006           4         100         800
       2006           5           0         800
       2006           6