二个表数据相当大!
t_acmain:
acno char 16
acbl demical'金额
flag char 1
t_aclist:
acno char 16
acbl demical'金额
现在需要把t_acmain中的acbl与t_aclist中acbl的金额相加,再保存到t_aclist中
二个表以acno一一对应,如果t_aclist中没有这个记录,就直接插入t_aclist否则就金额相加,再更新t_aclist
按照下面的方法:
sql="select * from t_acmain"
rst.open sql,conn,1,1
do while not rst.eof
sql="select * from t_aclist where acno='" & rst!acno & "'"
rstA.open sql,conn,1,1
if not rst.eof then
acbl=rst!acbl+rstA!acbl
sql="update t_aclist set acbl=" & acbl & " where acno='" & rst!acno & "'"
conn.execute sql
else
sql="insert into t_aclist(acno,acbl) values('" & rst!acno & "'," & rst!acbl & ")"
conn.execute sql
end if
rst.movenext
loop
不知道要花上多长时间,不知各位有没有其他解决方法?谢谢!
t_acmain:
acno char 16
acbl demical'金额
flag char 1
t_aclist:
acno char 16
acbl demical'金额
现在需要把t_acmain中的acbl与t_aclist中acbl的金额相加,再保存到t_aclist中
二个表以acno一一对应,如果t_aclist中没有这个记录,就直接插入t_aclist否则就金额相加,再更新t_aclist
按照下面的方法:
sql="select * from t_acmain"
rst.open sql,conn,1,1
do while not rst.eof
sql="select * from t_aclist where acno='" & rst!acno & "'"
rstA.open sql,conn,1,1
if not rst.eof then
acbl=rst!acbl+rstA!acbl
sql="update t_aclist set acbl=" & acbl & " where acno='" & rst!acno & "'"
conn.execute sql
else
sql="insert into t_aclist(acno,acbl) values('" & rst!acno & "'," & rst!acbl & ")"
conn.execute sql
end if
rst.movenext
loop
不知道要花上多长时间,不知各位有没有其他解决方法?谢谢!
SELECT t_aclist.acno, t_acmain.acbl + t_aclist.acbl AS acb1
FROM t_acmain INNER JOIN
t_aclist ON t_acmain.acno = t_aclist.acno
SET t.acbl= t.acbl+ s.acbl
FROM t_aclist t, t_acmain s
WHERE t.acno= s.acnoINSERT INTO t_actlist
SELECT t_acmain.acno,t_acmain.acbl
FROM t_acmain
WHERE t_acmain.acno NOT IN
(SELECT t_actlist.acno
FROM t_actlist)