如果通过mysql实现下面结果？
SQL怎么实现以下操作
首先，这是你想要的更新语句：
UPDATE Table1
SET SumOfLeak = (
SELECT SUM(NumOfLeak) FROM Table1 t2 WHERE t2.EnrolDate IN (
SELECT TOP 3 EnrolDate FROM Table1 t3 WHERE t3.EnrolDate <= t1.EnrolDate ORDER BY t3.EnrolDate desc
)
)
FROM Table1 t1
假设有以下表和数据：
create table Table1
(
EnrolDatedatetimeprimary key,
NumOfLeakint,
SumOfLeakint
)
insert into Table1 values('2010-01-06',0,null)
insert into Table1 values('2010-01-07',9,null)
insert into Table1 values('2010-01-08',1,null)
insert into Table1 values('2010-01-09',1,null)
insert into Table1 values('2010-01-10',1,null)
insert into Table1 values('2010-01-11',4,null)
insert into Table1 values('2010-01-12',3,null)
insert into Table1 values('2010-01-13',1,null)
执行更新后：
EnrolDate NumOfLeak SumOfLeak
----------------------- ----------- -----------
2010-01-06 00:00:00.000 0 0
2010-01-07 00:00:00.000 9 9
2010-01-08 00:00:00.000 1 10
2010-01-09 00:00:00.000 1 11
2010-01-10 00:00:00.000 1 3
2010-01-11 00:00:00.000 4 6
2010-01-12 00:00:00.000 3 8
2010-01-13 00:00:00.000 1 8
(8 行受影响)