求SQL更新语句
表1
===============================
BEGIN IN OUT END DATE
100 10 20 2008-07-01
20 30 2008-07-02
50 20 2008-07-03
表2
===============================
BEGIN IN OUT END DATE
100 10 20 90 2008-07-01
90 20 30 80 2008-07-02
80 50 20 110 2008-07-03
END = BEGIN + IN - OUT
BEGIN = 上一天END
请问如何用一条SQL将表1更新到表2?
[解决办法]
update table1 set BEGIN = (nvl(BEGIN,select END from table1 where DATE = trunc(DATE)-1)), END = (nvl(BEGIN,select END from table1 where DATE = trunc(DATE)-1)+IN-OUT);
不知道是否可以运行 手边没有oracle环境. 大概思路就是这个了，子查询应该可以优化.
[解决办法]
本人在mysql5.0下调试过，可行。
insert into test2
(select (select max(begin) from test) +
IFNULL((select sum(t.IN) - sum(t.OUT)
from test t
where t.DATE < tt.DATE),
0) begin,
tt.in,
tt.out,
(select max(begin) from test) +
(select sum(t.IN) - sum(t.OUT) from test t where t.DATE <= tt.DATE) end,
tt.date
from test tt)
[解决办法]
先select 后insert
[解决办法]
考虑用procedure来实现
mssql版本，用了cursor，破方法。
-- 建表create table t1( b int, i int, o int, e int, d datetime default getdate())-- 插入数据insert into t1 select 100,10,20,null,'2008-07-01' union allselect null,20,30,null,'2008-07-02' union allselect null,50,20,null,'2008-07-03' union allselect null,50,20,null,'2008-07-04' -- 存储过程create procedure prDoSomethingasdeclare @b int, @i int, @o int, @e int, @d datetimedeclare cur cursor forselect b,i,o,d from t1open curfetch next from cur into @b,@i,@o,@dwhile(@@fetch_status = 0)begin update t1 set e = @b + @i - @o where d = @d update t1 set b = @b + @i - @o where d = (select min(d) from t1 where d > @d) fetch next from cur into @b,@i,@o,@dendclose curdeallocate cur-- 执行procedureexec prDoSomething-- 查询select * from t1-- 清理 drop table t1drop procedure prDoSomething
