SQL：如何用前一行值填充空单元格？

发布时间：2020-12-12 06:35:01 所属栏目：MsSql教程来源：网络整理

导读：我需要在不使用循环和相关子查询的情况下使用SQL生成下表中的“required”列.这在SQL 2008中是否可行？ Date Customer Value Required Rule20100101 1 12 1220100101 2 0 If no value assign 020100101 3 32 3220100101 4 42 4220100101 5 15 1520100102 1 12

我需要在不使用循环和相关子查询的情况下使用SQL生成下表中的“required”列.这在SQL 2008中是否可行？

Date    Customer   Value   Required   Rule
20100101       1      12         12
20100101       2                  0   If no value assign 0
20100101       3      32         32
20100101       4      42         42
20100101       5      15         15
20100102       1                 12   Take last known value
20100102       2                  0   Take last known value
20100102       3      39         39
20100102       4                 42   Take last known value
20100102       5      16         16
20100103       1      13         13
20100103       2      24         24
20100103       3                 39   Take last known value
20100103       4                 42   Take last known value
20100103       5      21         21
20100104       1      14         14
20100104       2                 24   Take last known value
20100104       3                 39   Take last known value
20100104       4      65         65
20100104       5      23         23

基本上我正在为该客户填充具有最后知道值的空“值”单元格.请记住,最后一行可能没有有效值,因此您必须使用有效值从之前的行中选择它.

解决方法

法伊兹,

如果以下查询怎么样,它根据我的理解做你想做的事情.评论解释了每一步.看看联机丛书上的CTE.甚至可以更改此示例以使用SQL 2008的新MERGE命令.

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

    INSERT  INTO @Customers
    VALUES  ('20100101',1,12),('20100101',2,NULL),3,32),4,42),5,15),('20100102',39),16),('20100103',13),24),21),('20100104',14),65),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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!