sql咨询

[xjqstar]

表1
date1 dt1 dt2 dt3 dt4 get1 get2 get3 get4
20070101 1.2 1.4 1.5 0.9 0 0 0 0
20070102 1.5 1.3 1.7 1.1 0 0 0 0
20070103 2.4 2.7 2.7 2.2 0 0 0 0
20070105 2.5 2.8 3.0 2.4 0 0 0 0
20070107 10.1 10.9 10.9 10.1 0 0 0 0
20070108 11.2 11.2 11.6 10.3 0 0 0 0
这样一个表，更新这个表的后四个get值，更新公式如下：
要将当天的get1=当天的dt3-昨天的dt2
当天的get2=昨天的dt2-当天的dt4
当天的get3=前30天的get1之和（其实就是往前一个月的总和，注意date1时间是不连续的）
get4=前30天的get2之和

请问这样的该如何操作

[gahade]

date1列会重复吗?

[gahade]

没经过测试

update 表1
set get1=表1.dt3-(select dt2 from 表1 a where cast(a.date1 as datetime)=dateadd(day,-1,cast(表1.date1 as datetime))),
get2=(select b.dt2 from 表1 b where cast(b.date1 as datetime)=dateadd(day,-1,cast(表1.date1 as datetime)))-表1.dt4,
get3=(select sum(c.get1) from (select top 30 aa.get1 from 表1 aa where aa.date1<表1.date1 order by aa.date1 desc)c),
get4=(select sum(c.get2) from (select top 30 bb.get2 from 表1 bb where bb.date1<表1.date1 order by bb.date1 desc)d)
from 表1

[xjqstar]

不会重复

[肥胖的柠檬]

update 表1
set
get1 = 表1.dt3-isnull((select dt2 from 表1 a where a.date1=dateadd(dd,-1,表1.date1) ,0),
get2 = isnull((select dt2 from 表1 b where b.date1=dateadd(dd,-1,表1.date1) ,0)-表1.dt4,
get3 = (select sum(c.get1) from 表1 c where DATEDIFF(dd,表1.date1,c.date1) between 0 and 30),
get4 = (select sum(c.get2) from 表1 d where DATEDIFF(dd,表1.date1,d.date1) between 0 and 30)
from 表1

[lwl0606]

UPDATE TABLE1 SET GET1=(SELECT TABLE1.DT3-a.dt2 FROM TABLE1 A WHERE TABLE1.date1=DATEADD(DAY,1,A.DATE1 )),
get2=(SELECT a.dt2-TABLE1.dt4 FROM TABLE1 A WHERE TABLE1.date1=DATEADD(DAY,1,A.DATE1 ))
FROM TABLE1


UPDATE TABLE1 SET GET3=(SELECT SUM(isnull(GET1,0)) FROM TABLE1 A WHERE DATEADD(DAY,-30,TABLE1.DATE1 )<=A.DATE1 and TABLE1.DATE1>= A.DATE1),
get4=(SELECT sum(isnull(get2,0)) FROM TABLE1 A WHERE DATEADD(DAY,-30,TABLE1.DATE1 )<=A.DATE1 and TABLE1.DATE1>= A.DATE1)
FROM TABLE1
我感觉分两步比较简单