SQL:如何用前一行值填充空单元格?

费兹,

下面的查询怎么样?据我所知,它能满足您的要求。注释解释了每个步骤。看看网上图书的CTE。该示例甚至可以更改为使用新的针对SQL2008的MERGE命令。

/* Test Data & Table */
DECLARE @Customers TABLE
    (Dates datetime,
     Customer integer,
     Value integer) 

    INSERT  INTO @Customers
    VALUES  ('20100101', 1, 12),
        ('20100101', 2, NULL),
        ('20100101', 3, 32),
        ('20100101', 4, 42),
        ('20100101', 5, 15),
        ('20100102', 1, NULL),
        ('20100102', 2, NULL),
        ('20100102', 3, 39),
        ('20100102', 4, NULL),
        ('20100102', 5, 16),
        ('20100103', 1, 13),
        ('20100103', 2, 24),
        ('20100103', 3, NULL),
        ('20100103', 4, NULL),
        ('20100103', 5, 21),
        ('20100104', 1, 14),
        ('20100104', 2, NULL),
        ('20100104', 3, NULL),
        ('20100104', 4, 65),
        ('20100104', 5, 23) ;

/* CustCTE - This gives us a RowNum to allow us to build the recursive CTE CleanCust */
WITH    CustCTE
          AS (SELECT    Customer,
                        Value,
                        Dates,
                        ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Dates) RowNum
              FROM      @Customers),

/* CleanCust - A recursive CTE. This runs down the list of values for each customer, checking the Value column, if it is null it gets the previous non NULL value.*/
        CleanCust
          AS (SELECT    Customer,
                        ISNULL(Value, 0) Value, /* Ensure we start with no NULL values for each customer */
                        Dates,
                        RowNum
              FROM      CustCte cur
              WHERE     RowNum = 1
              UNION ALL
              SELECT    Curr.Customer,
                        ISNULL(Curr.Value, prev.Value) Value,
                        Curr.Dates,
                        Curr.RowNum
              FROM      CustCte curr
              INNER JOIN CleanCust prev ON curr.Customer = prev.Customer
                                           AND curr.RowNum = prev.RowNum + 1)

/* Update the base table using the result set from the recursive CTE */
    UPDATE trg
    SET Value = src.Value
    FROM    @Customers trg
    INNER JOIN CleanCust src ON trg.Customer = src.Customer
                                AND trg.Dates = src.Dates

/* Display the results */
SELECT * FROM @Customers

这就是 Last non-null puzzle ,“以下是几种优雅的解决方案之一:

SparseTable 使用列Date、Customer、Value,然后:

with C as
(select *,
    max(case when Value is not null then [Date] end)
        over (partition by Customer order by [Date] rows unbounded preceding) as grp
 from SparseTable
)
insert into FullTable
select *, 
    max(Value) over (partition by Customer, grp order by [Date] rows unbounded preceding) as Required
from C

Value 无法向前填充 NULL ,这样你就可以

update FullTable set Required = 0 where Required is null

我需要制作专栏 使用SQL而不使用循环和 相关子查询。是这个吗 在SQL 2008中可能吗?

不可能的。点。不可能在任何基于SQL的服务器上,包括oracle。

考虑到你的约束条件,我不确定以下这些是否算数,但它能完成工作。

DECLARE @Customers TABLE (Date DATETIME, Customer INTEGER, Value INTEGER)

INSERT INTO @Customers VALUES ('20100101', 1, 12  )       
INSERT INTO @Customers VALUES ('20100101', 2, NULL)           
INSERT INTO @Customers VALUES ('20100101', 3, 32  ) 
INSERT INTO @Customers VALUES ('20100101', 4, 42  ) 
INSERT INTO @Customers VALUES ('20100101', 5, 15  ) 
INSERT INTO @Customers VALUES ('20100102', 1, NULL) 
INSERT INTO @Customers VALUES ('20100102', 2, NULL)
INSERT INTO @Customers VALUES ('20100102', 3, 39  )
INSERT INTO @Customers VALUES ('20100102', 4, NULL)
INSERT INTO @Customers VALUES ('20100102', 5, 16  )
INSERT INTO @Customers VALUES ('20100103', 1, 13  )
INSERT INTO @Customers VALUES ('20100103', 2, 24  )
INSERT INTO @Customers VALUES ('20100103', 3, NULL)
INSERT INTO @Customers VALUES ('20100103', 4, NULL)
INSERT INTO @Customers VALUES ('20100103', 5, 21  )
INSERT INTO @Customers VALUES ('20100104', 1, 14  )
INSERT INTO @Customers VALUES ('20100104', 2, NULL)
INSERT INTO @Customers VALUES ('20100104', 3, NULL)
INSERT INTO @Customers VALUES ('20100104', 4, 65  )
INSERT INTO @Customers VALUES ('20100104', 5, 23  )

查询

SELECT  c.Date
        , c.Customer
        , Value = COALESCE(c.Value, cprevious.Value, 0)
FROM    @Customers c
        INNER JOIN (
          SELECT  c.Date
                  , c.Customer
                  , MaxDate = MAX(cdates.Date)
          FROM    @Customers c
                  LEFT OUTER JOIN (
                    SELECT  Date
                            , Customer
                    FROM    @Customers
                  ) cdates ON cdates.Date < c.Date AND cdates.Customer = c.Customer
          GROUP BY
                  c.Date, c.Customer
        ) cmax ON cmax.Date = c.Date AND cmax.Customer = c.Customer                  
        LEFT OUTER JOIN @Customers cprevious ON cprevious.Date = cmax.MaxDate AND cprevious.Customer = cmax.Customer
ORDER BY
        1, 2, 3        

UPDATE  @Customers 
SET     Value = c2.Value 
OUTPUT  Inserted.* 
FROM    @Customers c 
        INNER JOIN ( 
          SELECT  c.Date
                  , c.Customer
                  , Value = COALESCE(c.Value, cprevious.Value, 0)
          FROM    @Customers c
                  INNER JOIN (
                    SELECT  c.Date
                            , c.Customer
                            , MaxDate = MAX(cdates.Date)
                    FROM    @Customers c
                            LEFT OUTER JOIN (
                              SELECT  Date
                                      , Customer
                              FROM    @Customers
                            ) cdates ON cdates.Date < c.Date AND cdates.Customer = c.Customer
                    GROUP BY
                            c.Date, c.Customer
                  ) cmax ON cmax.Date = c.Date AND cmax.Customer = c.Customer                  
                  LEFT OUTER JOIN @Customers cprevious ON cprevious.Date = cmax.MaxDate AND cprevious.Customer = cmax.Customer
        ) c2 ON c2.Date = c.Date 
                AND c2.Customer = c.Customer 

在同一个表中,如果日期小于当前日期,并且值为非空,按date desc(限制1)排序,空时返回零,那么左外联接如何(目前没有可供测试的服务器)。除非这算作子查询。。。