--try next:
update t1 set t1.JiFenAll = (t1.JiFenAll - t2.JiFen)
from A t1
,(
select MID,sum(JiFen) JiFen from B
where Date_ < 某日期
group by MID
) t2
where t1.MID = t2.MID
update t1 set t1.JiFenAll = (t1.JiFenAll - t2.JiFen)
from A t1
,(
select MID,sum(JiFen) JiFen from B
where Date_ < 某日期
group by MID
) t2
where t1.MID = t2.MID
解决方案 »
- 请教大神!如何使程序支持多种数据库?! 【所有积分就90了 大家见谅】
- 菜鸟提问【送分题】,如何创建自定义函数
- 一条SQL 语句
- 请问在ORACLE10G中如何访问远程的数据库啊
- 如何建立em?
- ${ORACLE_HOME}/precomp/lib32中的文件有什么作用,能不能举个例子
- database configuration assistant not work?help help
- 我要想把一个结果集原封不动的插入到一个表里该怎么办最简洁?
- 什么地方可以下载Designer?
- 又是一个密码的问题?(看大家能否解答)
- 紧急求救:关于oracle数据库的恢复问题
- 知道的请支持——Oracle9i中在SQL*PLUS界面中怎样导出存储过程?
from b where date_<=to_date('2004-01-01','yyyy-mm-dd')
and b.mid=a.mid)
where exists (select 1 from b where b.mid=a.mid);
sql server的。
update A set JiFenAll =
JiFenAll -
nvl(
(
select sum(JiFen) from B
where Date_ < '某日期'
where MID = A.MID
),0)
--
把语句帖出,汗~~
update vip_card set JIFEN_BALANCE=JIFEN_BALANCE-(select sum(amt)
from vip_consume where date_<'28-SEP-02'
and vip_consume.cardid=vip_card.cardid)
where exists (select 1 from vip_consume where vip_card.cardid=vip_consume.cardid);
/
update vip_card set JIFEN_BALANCE =
JIFEN_BALANCE -
nvl(
(
select sum(amt) from vip_consume
where date_ < '28-SEP-03'
and cardid = vip_card.cardid group by cardid
),0)
from vip_consume where date_<to_date('2002-09-28','yyyy-mm-dd')
and vip_consume.cardid=vip_card.cardid)
where exists (select 1 from vip_consume where vip_card.cardid=vip_consume.cardid);
/
update vip_card set JIFEN_BALANCE =
JIFEN_BALANCE -
nvl(
(
select sum(amt) from vip_consume
where date_ < to_date('2002-09-28','yyyy-mm-dd')
and cardid = vip_card.cardid
),0)/
JIFEN_BALANCE -
nvl(
(
select sum(amt) from vip_consume
where date_ < to_date('2002-09-28','yyyy-mm-dd')
and cardid = vip_card.cardid
),0)
/
--这个在我机器上通过
create table BBB(MID number(2),JiFen number(5),Date_ date);insert into AAA values(1,500);
insert into AAA values(2,200);
insert into AAA values(3,250);
insert into AAA values(4,300);insert into BBB values(1,10,sysdate);
insert into BBB values(1,20,sysdate);
insert into BBB values(1,30,sysdate);
insert into BBB values(2,100,sysdate);
insert into BBB values(3,100,sysdate);
insert into BBB values(3,200,sysdate);select * from aaa;
select * from bbb;update AAA a set JiFenAll =
JiFenAll -
nvl(
(
select sum(JiFen) from BBB
--where Date_ < 某日期
where MID = a.MID
),0);select * from aaa;drop table AAA;
drop table BBB;
12:20:48 SQL> select * from aaa; MID JIFENALL
---------- ----------
1 500
2 200
3 250
4 300已用时间: 00: 00: 00.03
12:20:49 SQL> select * from bbb; MID JIFEN DATE_
---------- ---------- ----------
1 10 03-9月 -04
1 20 03-9月 -04
1 30 03-9月 -04
2 100 03-9月 -04
3 100 03-9月 -04
3 200 03-9月 -04已选择6行。已用时间: 00: 00: 00.66
12:20:50 SQL>
12:20:51 SQL> update aaa set jifenall=jifenall-(select sum(jifen) from
12:22:03 2 bbb where bbb.mid=aaa.mid)
12:22:19 3 where exists (select 1 from bbb where bbb.mid=aaa.mid);已更新3行。已用时间: 00: 00: 00.41
12:22:38 SQL> select * from aaa; MID JIFENALL
---------- ----------
1 440
2 100
3 -50
4 300已用时间: 00: 00: 00.75
12:23:03 SQL> commit;提交完成。已用时间: 00: 00: 00.07