在线求一SQL语句，马上结贴。
我用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:
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
[解决办法]
仅仅针对以上数据吗,还是说表中没有哪个月份的记录,我在查询时就自动给补上该月份,
[解决办法]
--可能在原來就處理，更好一些，如果對結果處理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
[解决办法]
就你目前的说法,必须使用临时表,可以建立一个,也可以象那样在查询的时候生成一个.
[解决办法]
学习~~
[解决办法]
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<=@maxMonthbegin insert into @Months values(@i) set @i=@i+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)*/
[解决办法]
CREATE TABLE t
(
[Year] INT,
[Month] INT,
Amount INT,
YTD INT
)
INSERT INTO T
SELECT 2006,1, 200, 200 union all
SELECT 2006,2, 300, 500 union all
SELECT 2006,3, 200, 700 union all
SELECT 2006,4, 100, 800 union all
SELECT 2007,1, 50, 50 union all
SELECT 2007,2, 10, 60 UNION ALL
SELECT 2008,1, 40, 50
DECLARE @i INT
SELECT @i = 9
SET ROWCOUNT @i
SELECT [ID] = IDENTITY(INT,1,1) INTO # FROM SYSOBJECTS
SET ROWCOUNT 0
SELECT [year],[Month],Amount = ISNULL(Amount,0),
[YTD] = CASE WHEN YTD IS NULL
THEN (SELECT MAX(YTD) FROM
(
SELECT A.[year],[Month]=A.[ID],B.Amount,B.YTD FROM
(
SELECT * FROM (SELECT [year] FROM t GROUP BY [year]) A, # B
) A FULL JOIN t B ON A.year = B.year AND A.ID = B.Month
) B WHERE [Month]<A.[Month] AND [Year] = A.[Year])
ELSE YTD
END
FROM
(
SELECT A.[year],[Month]=A.[ID],B.Amount,B.YTD FROM
(
SELECT * FROM (SELECT [year] FROM t GROUP BY [year]) A, # B
) A FULL JOIN t B ON A.year = B.year AND A.ID = B.Month
) A
DROP TABLE #
DROP TABLE t
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
2008 1 40 50
2008 2 0 50
2008 3 0 50
2008 4 0 50
2008 5 0 50
2008 6 0 50
2008 7 0 50
2008 8 0 50
2008 9 0 50
（所影响的行数为 27 行）
警告: 聚合或其它 SET 操作消除了空值。
