日期:2014-05-18 浏览次数:20416 次
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
------解决方案--------------------
仅仅针对以上数据吗,还是说表中没有哪个月份的记录,我在查询时就自动给补上该月份,
------解决方案--------------------
--可能在原來就處理,更好一些,如果對結果處理 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
------解决方案--------------------
就你目前的说法,必须使用临时表,可以建立一个,也可以象那样在查询的时候生成一个.
------解决方案--------------------
学习~~
------解决方案--------------------
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