我用SQL语句查询出来的结果如下：
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月和4月的 。我能否通过SQL语句使查询后的结果变成2007年自动补充 3月和4月 的数据（自动补充0或者空值即可），使结果如下：
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
请教大侠如何写这个语句？谢谢各位了。
------解决方案--------------------
try:
- SQL code
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) across join (select distinct Month from test) bleft join test con 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 ytdfrom ( select distinct t1.[year],t2.[month] from ( select distinct [month] from #t ) t2 left join #t t1 on 1=1 ) A left join #t Bon A.[year]=B.[year] and A.[month]=B.[month]order by A.[year],A.[month]/*year month amount ytd ----------- ----------- ----------- ----------- 2006 1 200 2002006 2 300 5002006 3 200 7002006 4 100 8002007 1 50 502007 2 10 602007 3 0 602007 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 intselect @maxMonth=9, @i=1while @i<[email protected]begin insert into @Months values(@i) set @[email protected]+1endselect 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.Monthunion allselect * 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 0 800 2006 7 0 800 2006 8 0 800 2006 9 0 800 2007 1 50 50 2007 2 10 60 2007 3 0 60 2007 4 0 60 2007 5 0 60 2007 6 0 60 2007 7 0 60 2007 8 0 60 2007 9 0 60(18 row(s) affected)*/