day+2列中的乘积值

Question

我有一张这样的桌子：

date       | cpo       | production_ms | cpo_sell_profit
=======================================================
2016-08-01 | 7146      | 75187         |  
2016-08-02 | 7299      | 68925         |  
2016-08-03 | 7330      | 65534         |  
2016-08-04 | 7416      | 72133         |  
2016-08-05 | 7563      | 71442         |  

我想填充cpo_sell_profit列，但我不知道如何用来自cpo的值乘以production_ms值，其中cpo.date = day +2，以填充cpo_sell_profit.

示例：

75187从2016-08-01乘以7330从2016-08-03

提前感谢

Answer 1

;with cte as (
    Select *
          ,NewVal = production_ms* Lead(cpo,2,0) over (Order By date)
     From  YourTable
)
Update cte Set cpo_sell_profit = NewVal

Select * from YourTable

返回

date        cpo     production_ms   cpo_sell_profit
2016-08-01  7146    75187           551120710
2016-08-02  7299    68925           511147800
2016-08-03  7330    65534           495633642
2016-08-04  7416    72133           0
2016-08-05  7563    71442           0

不确定您要对超出范围的记录做什么。当前设置为零，但是如果输入1，则会得到production_ms ..。见铅(cpo，2，0)

Answer 2

你可以试试这个

UPDATE t2
SET t2.cpo_sell_profit = t1.production_ms *t2.cpo
From yourtable t1 inner join yourtable t2 on t1.date = dateadd(dd,2,t2.date)

Answer 3

使用LEAD函数

;WITH cte
     AS (SELECT *,
                Lead(cpo, 2)OVER(ORDER BY dates) AS next_val
         FROM   (VALUES ('2016-08-01',7146,75187 ),
                        ('2016-08-02',7299,68925 ),
                        ('2016-08-03',7330,65534 ),
                        ('2016-08-04',7416,72133 ),
                        ('2016-08-05',7563,71442 )) tc(dates, cpo, production_ms ))
SELECT dates,
       cpo,
       production_ms,
       production_ms * next_val
FROM   cte 

结果:

╔════════════╦══════╦═══════════════╦═════════════════╗
║   dates    ║ cpo  ║ production_ms ║ cpo_sell_profit ║
╠════════════╬══════╬═══════════════╬═════════════════╣
║ 2016-08-01 ║ 7146 ║         75187 ║ 551120710       ║
║ 2016-08-02 ║ 7299 ║         68925 ║ 511147800       ║
║ 2016-08-03 ║ 7330 ║         65534 ║ 495633642       ║
║ 2016-08-04 ║ 7416 ║         72133 ║ NULL            ║
║ 2016-08-05 ║ 7563 ║         71442 ║ NULL            ║
╚════════════╩══════╩═══════════════╩═════════════════╝

如果希望在没有2+dates的情况下使用production_ms的相同值，那么使用ISNULL

SELECT dates,
       cpo,
       production_ms,
       production_ms * ISNULL(next_val,1)
FROM   cte 

结果:

╔════════════╦══════╦═══════════════╦═════════════════╗
║   dates    ║ cpo  ║ production_ms ║ cpo_sell_profit ║
╠════════════╬══════╬═══════════════╬═════════════════╣
║ 2016-08-01 ║ 7146 ║         75187 ║       551120710 ║
║ 2016-08-02 ║ 7299 ║         68925 ║       511147800 ║
║ 2016-08-03 ║ 7330 ║         65534 ║       495633642 ║
║ 2016-08-04 ║ 7416 ║         72133 ║           72133 ║
║ 2016-08-05 ║ 7563 ║         71442 ║           71442 ║
╚════════════╩══════╩═══════════════╩═════════════════╝