从日期排序列表中查找以前的非零值(Finding the previous non-zero val

我运行MS-SQL Server 2008 R2中我有一个表，如下类似的设置。有日期中的价格为0; 但其占位符较前期意味着价格没有变化。我可以用COALESCE或将使用最近的一个日期的价格值不为0的另一个功能？

**项目名称| 日期| 价钱 **

Item1    |  01/01/2013 | 10.00
Item1    |  02/01/2013 | 9.50
Item1    |  03/01/2013 | 0
Item1    |  04/01/2013 | 9.50
Item1    |  05/01/2013 | 8.00
Item1    |  06/01/2013 | 8.00
Item1    |  07/01/2013 | 6.00
Item1    |  08/01/2013 | 6.00
Item1    |  09/01/2013 | 0
Item2    |  01/01/2013 | 50.00
Item2    |  02/01/2013 | 45.00
Item2    |  03/01/2013 | 35.00
Item2    |  04/01/2013 | 30.00
Item2    |  05/01/2013 | 25.00
Item2    |  06/01/2013 | 0
Item2    |  07/01/2013 | 10.00
Item2    |  08/01/2013 | 0
Item2    |  09/01/2013 | 0

所需的输出**项目名称| 日期| 价钱 **

Item1    |  01/01/2013 | 10.00
Item1    |  02/01/2013 | 9.50
Item1    |  03/01/2013 | 9.50
Item1    |  04/01/2013 | 9.50
Item1    |  05/01/2013 | 8.00
Item1    |  06/01/2013 | 8.00
Item1    |  07/01/2013 | 6.00
Item1    |  08/01/2013 | 6.00
Item1    |  09/01/2013 | 6.00
Item2    |  01/01/2013 | 50.00
Item2    |  02/01/2013 | 45.00
Item2    |  03/01/2013 | 35.00
Item2    |  04/01/2013 | 30.00
Item2    |  05/01/2013 | 25.00
Item2    |  06/01/2013 | 25.00
Item2    |  07/01/2013 | 10.00
Item2    |  08/01/2013 | 10.00
Item2    |  09/01/2013 | 10.00

这里是你的样品台

SELECT * INTO #TEMP
FROM
(
SELECT 'Item1' [Item Name],      '01/01/2013' [Date], 10.00 [Price]
UNION ALL
SELECT 'Item1',      '02/01/2013', 9.50
UNION ALL
SELECT 'Item1',      '03/01/2013', 0
UNION ALL
SELECT 'Item1',      '04/01/2013', 7.50
UNION ALL
SELECT 'Item1',     '05/01/2013', 0.00
UNION ALL
SELECT 'Item1',     '06/01/2013', 0.00
UNION ALL
SELECT 'Item1',     '07/01/2013', 11.00
UNION ALL
SELECT 'Item2',      '01/01/2013', 0.00
UNION ALL
SELECT 'Item2',     '02/01/2013', 12.00
UNION ALL
SELECT 'Item2',      '03/01/2013', 0.00
UNION ALL
SELECT 'Item2',      '04/01/2013', 0.00
UNION ALL
SELECT 'Item2',     '05/01/2013', 13.00
)TAB

而结果查询

 SELECT [Item Name],[Date],
CASE WHEN [Price]=0 THEN 
    ISNULL((SELECT TOP 1 [Price]
     FROM #TEMP T2 
     WHERE CAST(T2.[Date] AS DATE)<T1.[Date] AND T1.[Item Name]=T2.[Item Name] AND T2.[Price]>0
     ORDER BY CAST(T2.[Date] AS DATE) DESC),0) 
    ELSE [Price] END [Price]
FROM #TEMP T1

你的结果是这里