SQL查询可填补跨时间缺少的空白并获取最后一个非空值

我创建了 SQL提琴此解决方案供您使用.

I have created a SQL Fiddle of this solution for you to play with.

基本上，它会创建一个工作表@Months，然后交叉联接将在您的数据集中所有年份.这将产生所有年份所有月份的完整列表.然后，我将您示例中提供的Test数据(表名为TEST-有关架构的SQL小提琴)加入到该列表中，从而为我提供了包含值的月份的完整列表.要克服的下一个问题是，如果这几个月没有任何值，则使用最近几个月的值.为此，我使用了一个相关的子查询，即仅在它与具有值的行的最大Rank匹配时才将tblValues重新加入自身.这样就给出了完整的结果集！

Essentially it creates a Work Table @Months and then Cross joins this will all years in your data set. This produces a complete list of all months for all years. I then left join the Test data provided in your example (Table named TEST - see SQL fiddle for schema) back into this list to give me a complete list with Values for the months that have them. The next issue to overcome was using the last months values if this months didn't have any. For that, I used a correlated sub-query i.e. joined tblValues back on itself only where it matched the maximum Rank of a row which has a value. This then gives a complete result set!

如果要按年/月进行过滤，可以将其添加到WHERE子句中，该子句位于最终的订购依据"之前.

If you want to filter by year\month you can add this into a WHERE clause just before the final Order By.

享受！

测试架构

CREATE TABLE TEST( Month tinyint, Year int, Value int)

INSERT INTO TEST(Month, Year, Value)
VALUES
   (1,2013,100),
   (4,2013,101),
   (8,2013,102),
   (2,2014,103),
   (4,2014,104)

查询

DECLARE @Months Table(Month tinyint)
Insert into @Months(Month)Values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);


With tblValues as (
  select Rank() Over (ORDER BY y.Year, m.Month) as [Rank], 
          m.Month, 
          y.Year, 
          t.Value
  from @Months m
  CROSS JOIN ( Select Distinct Year from Test ) y
  LEFT JOIN Test t on t.Month = m.Month and t.Year = y.Year
  )
Select t.Month, t.Year, COALESCE(t.Value, t1.Value) as Value
from tblValues t
left join tblValues t1 on t1.Rank = (
            Select Max(tmax.Rank)
            From tblValues tmax 
            Where tmax.Rank < t.Rank AND tmax.Value is not null)

Order by t.Year, t.Month