时间戳

首页 > 解决方案 > 根据其他两列的累积和更新列值

sql - 根据其他两列的累积和更新列值

问题描述

我有下表

缓冲

| id    | from_Time     | To_time   | v1    | v2    | v3    | time Stamp            |
|-----  |-----------    |---------  |----   |----   |----   |---------------------  |
| 104   | 10            | 11        | 9     | 8     |       | 2020-12-02 11:03:01   |
| 103   | 9             | 10        | 10    | 11    |       | 2020-12-02 10:03:01   |
| 102   | 8             | 9         | 13    | 13    |       | 2020-12-02 09:03:01   |
| 101   | 7             | 8         | 12    | 11    |       | 2020-12-02 08:03:01   |
| 100   | 6             | 7         | 10    | 8     |       | 2020-12-02 07:03:01   |
| 99    | 5             | 6         | 0     | 0     |       | 2020-12-02 06:03:01   |
| 98    | 4             | 5         | 0     | 0     |       | 2020-12-02 05:03:01   |
| 97    | 3             | 4         | 0     | 0     |       | 2020-12-02 03:03:15   |
| 96    | 2             | 3         | 0     | 0     |       | 2020-12-02 04:03:02   |
| 95    | 1             | 2         | 0     | 0     |       | 2020-12-02 02:03:01   |
| 94    | 0             | 1         | 0     | 0     |       | 2020-12-02 01:03:00   |
| 93    | 23            | 0         | 0     | 0     | 2     | 2020-12-02 00:03:00   |
| 92    | 22            | 23        | 0     | 4     | 2     | 2020-12-01 23:03:01   |
| 91    | 21            | 22        | 14    | 13    | 6     | 2020-12-01 22:03:00   |
| 90    | 20            | 21        | 10    | 8     | 5     | 2020-12-01 21:03:00   |
| 89    | 19            | 20        | 7     | 5     | 3     | 2020-12-01 20:03:01   |
| 88    |               |           |       |       |       |                       |
| 87    |               |           |       |       |       |                       |
| 86    |               |           |       |       |       |                       |
| 85    | 23            | 0         | 0     | 0     | 1     | 2020-12-01 01:03:00   |

我想编写一个更新查询来更新列 v3。V3列计算如下

前一天值介于 23 和 0 之间 + 今天日期的第 V1 列的累计总数 - 今天日期的第 V2 列的累计总数

ID 91 的示例

V1 的累计总数是 31 V2 的累计总数是 26 前一天的值在 23 和 0 之间是 1

所以 ID 91 的 V3 值等于 1+31-26 = 6

我想编写一个带有 where 子句的更新查询，以将 ID 的数据从 104 更新到 100。

我可以使用以下查询获得 V3 的新值

select   T1.id,
    T1.from_time,
    T1.To_time,
    T1.v1
     ,T1.v2
     ,T1.cumm_v1,
     T1.cumm_v2
     ,T1.cumm_v1-T1.cumm_v2 + (
       select v3 from Buffer where CAST([time Stamp] as DATE)  = '20201202' and from_time = 23 and To_time = 0) 
       as V3_New
from (
SELECT 
  id,
  from_time,
  To_time,
  v1,
  v2,
  [time Stamp]
    ,SUM(v1) OVER(ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumm_v1
    ,SUM(v2) OVER(ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumm_v2
FROM Buffer where CAST([time Stamp]  as DATE)  = '20201202'
       ) as T1

我在将其转换为更新查询时遇到问题。

http://sqlfiddle.com/#!18/abaa1/2

标签： sqlsql-serversql-server-2012

解决方案

update T1
set v3 = 
T1.cumm_v1-T1.cumm_v2 + (
       select v3 from Buffer where CAST([time Stamp] as DATE)  = '20201202' and from_time = 23 and To_time = 0) 
       --as V3_New
from (
SELECT 
  id,
  from_time,
  To_time,
  v1,
  v2,
  [v3], --<-- for the update
  [time Stamp]
    ,SUM(v1) OVER(ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumm_v1
    ,SUM(v2) OVER(ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumm_v2
FROM Buffer where CAST([time Stamp]  as DATE)  = '20201202'
       ) as T1
where id between 100 and 104 --ID from 104 to 100.

推荐阅读