用其他列的值更新表列

Question

我有一张这样的桌子：

Client | Date       | Value 1 | Value 2 |
1       2013-11-08    159      159
1       2013-11-09    254      254
1       2013-12-05    512      512
1       2014-01-02    1200     1200
2       2013-11-10    189      189
2       2013-11-15    289      289
2       2013-12-22    585      585
2       2014-01-06    1650     1650

我需要更新SQL中的表，如下所示：

Client | Date       | Value 1 | Value 2 |
1       2013-11-08    159      1200
1       2013-11-09    254      1200
1       2013-12-05    512      1200
1       2014-01-02    1200     1200
2       2013-11-10    189      1650
2       2013-11-15    289      1650
2       2013-12-22    585      1650
2       2014-01-06    1650     1650

其想法是，对于每个客户端，Value 2将成为Value 1，而Date是最近出现的。

Answer 1

在Server中，最好使用CTE和UPDATE语句。下面的查询演示了您需要做的事情的语法。您所要做的就是替换表名和列名。

;WITH    MyUpdate
          AS ( SELECT ClientId
                   ,Value1
                   ,ROW_NUMBER() OVER ( PARTITION BY ClientId ORDER BY MyDate DESC ) AS RowNum
                FROM MyTable)
    UPDATE MyTable
        SET MyTable.Value2 = MyUpdate.Value1
        FROM MyTable
        INNER JOIN MyUpdate
            ON MyUpdate.ClientID = MyTable.ClientID
               AND RowNum = 1

Answer 2

试试这个：

UPDATE TABLE1 T2 SET Value2 =
(SELECT T1.Value2 FROM TABLE1 T1 WHERE T1.Client = T2.Client AND
T1.Date = (SELECT MAX(T3.Date) FROM TABLE1 T3 
           WHERE T2.Client = T3.Client GROUP BY Client));

甲骨文