请教SQL语句，查询每月数据，但其中有几个月的没有，要取前月的数据。

[glvicky]

ID Number Price Year Month
1 1 123.00 2013 2
2 1 182.00 2013 1
3 1 141.00 2012 12
4 1 162.00 2012 7
5 1 160.00 2012 1

数据格式如上,要查询Number为1的产品的最近1年的价格，如果某月为空，则取前面有价格的月份，如查询时间2013年1月，编号为1的产品最一年的价格结果应为如下：
Number Pirce Year Month
1 182.00 2013 1
1 141.00 2012 12
1 162.00 2012 11
1 162.00 2012 10
1 162.00 2012 9
1 162.00 2012 8
1 162.00 2012 7
1 160.00 2012 6
...
1 160.00 2012 2

请问存储过程应该如何实现？

[我腫了]

USE test
GO

-->生成表tb

if object_id('tb') is not null 
	drop table tb
Go
Create table tb([ID] smallint,[Number] smallint,[Price] numeric(5,2),[Year] smallint,[Month] smallint)
Insert into tb
Select 1,1,123.00,'2013',2
Union all Select 2,1,182.00,'2013',1
Union all Select 3,1,141.00,'2012',12
Union all Select 4,1,162.00,'2012',7
Union all Select 5,1,160.00,'2012',1
Go

DECLARE 
	@Number SMALLINT
	,@Date DATETIME

SET @Number=1



SET @Date=GETDATE()

;WITH t AS (
	SELECT 
			DATEADD(mm,1,LTRIM(a.Year)+RIGHT(100+b.number,2)+'01') AS Date
			,a.Year
			,b.number AS Month
		FROM 
			(
				SELECT 
						Year
						,MIN(Month) AS mix_Month
						,MAX(Month) AS max_Month 
					FROM tb 
					WHERE Number=@Number 
					GROUP BY Year
			) AS a
			,master.dbo.spt_values AS b
		WHERE b.number BETWEEN a.mix_Month AND a.max_Month
			AND b.type='P'
),t2 AS (
	select 
			@Number AS Number
			,a.Price
			,t.Year
			,t.Month
			,t.Date
		from 
			(
				SELECT 
						Number
						,Price
						,Year
						,Month
						,DATEADD(mm,1,LTRIM(Year)+RIGHT(100+Month,2)+'01') AS Date 
					FROM tb 
					WHERE Number=@Number
			) AS a
			RIGHT JOIN t ON a.Date=t.Date
)
SELECT 
		a.Number
		,ISNULL(a.Price,b.Price) AS Price
		,a.Year
		,a.Month
	FROM t2 AS a
		OUTER APPLY(SELECT Price
						FROM t2 AS x
						WHERE x.Date=(SELECT MAX(Date) FROM t2 AS o WHERE o.Date<a.Date AND o.Price IS NOT NULL)
				) AS b
	WHERE a.Date<=@Date AND a.Date>=DATEADD(yy,-1,@date)
	ORDER BY a.Date DESC
	
/*
Number Price   Year   Month
------ ------- ------ -----------
1      182.00  2013   1
1      141.00  2012   12
1      162.00  2012   11
1      162.00  2012   10
1      162.00  2012   9
1      162.00  2012   8
1      162.00  2012   7
1      160.00  2012   6
1      160.00  2012   5
1      160.00  2012   4
1      160.00  2012   3
1      160.00  2012   2
*/

[glvicky]

非常感谢~··