解决方案 »
- job调用的程序出现分布式事务错误ora-02050 ora-02056 ora-020563错误
- Oracle手工建库 错误ORA-01092
- Linux下双网卡连接数据的问题
- 问一个sql语句如何写?
- oracle 高手来看看这个死锁问题。(多线程往数据库中查询ID,如果查询不到根据输入创建一条记录,并返回对应ID)
- oracle存储过程的问题
- ORACLE中如何实现字段描述?急`````(参与有分)
- 终于生星星了。散200,大家都拿些分,中午节
- 相对复杂的竖表转横表+动态时间比较
- 客户端电脑不识别oracle数据库中文字符
- function is in an invalid state
- 写个Full join句子
a字段000000的,
在更新a字段010400的
update tab1 B set b=b-(select max(b) from tab1 A where A.b=A.c and A.a=010400) ,
c=c-(select max(c) from tab1 A where A.b=A.c and A.a=010400)
where a=010000;update tab1 B set b=b-(select max(b) from tab1 A where A.b=A.c and A.a=010400) ,
c=c-(select max(c) from tab1 A where A.b=A.c and A.a=010400)
where a=000000;update tab1 B set b=0 ,c=0
where a=000000 and b=c;
(
a VARCHAR2(20),
b NUMBER(4),
c NUMBER(4),
d VARCHAR2(20)
);
INSERT INTO T134 VALUES('000000', 20, 20, 'a');
INSERT INTO T134 VALUES('010000', 15, 15, 'a');
INSERT INTO T134 VALUES('010400', 5, 5, 'a');INSERT INTO T134 VALUES('000000', 25, 25, 'b');
INSERT INTO T134 VALUES('010000', 15, 15, 'b');
INSERT INTO T134 VALUES('010400', 5, 5, 'b');INSERT INTO T134 VALUES('000000', 20, 20, 'c');
INSERT INTO T134 VALUES('010000', 15, 15, 'c');
INSERT INTO T134 VALUES('010400', 5, 0, 'c');-- 更新a = '0000000' 和a='010000'的行
UPDATE T134 T1 SET b = b - (SELECT b FROM T134 T2 WHERE b = c AND a = '010400' AND T2.d = T1.d),
c = c - (SELECT c FROM T134 T3 WHERE b = c AND a = '010400' AND T3.d = T1.d)
WHERE (a = '000000' OR a = '010000') AND
EXISTS(SELECT 1 FROM T134 T4 WHERE b = c AND a = '010400' AND T4.d = T1.d)
-- 更新 a = '010400'的行
UPDATE T134 SET b = 0, c = 0 WHERE b = c AND a = '010400';
实测结果:
还有个问题:
000000 25 25 c
010000 15 15 c
010400 5 0 c
000000 25 25 d
010000 15 15 d
010400 5 -5 d
000000 25 25 e
010000 15 15 e
010400 5 7 e
就是当a字段为010400时,b<>c的时候,也要进行一次处理,就是如果b-c>0则b=b-c,c=0,如果b-c<0则c=c-b,b=0
010000和000000的b,c字段也要处理。
当010400,b-c>0时 010000和000000的c=c减去010400的c值,b=b加上010400的c值
当b-c<0时, 010000和000000的c=c减去010400的b值,b=b减去010400的b值结果应该为: 000000 25 25 c
010000 15 15 c
010400 5 0 c
000000 30 30 d
010000 20 20 d
010400 10 0 d
000000 20 20 e
010000 10 10 e
010400 0 2 e
帮忙看下我这么写,行么?
UPDATE T134 T1 SET b = b - (SELECT case when b>c then c else b end FROM T134 T2 WHERE b <> c AND a = '010400' AND T2.d = T1.d),
c = c - (SELECT case when b>c then c else b end FROM T134 T3 WHERE b <> c AND a = '010400' AND T3.d = T1.d)
WHERE (a = '000000' OR a = '010000') AND
EXISTS(SELECT 1 FROM T134 T4 WHERE b <> c AND a = '010400' AND T4.d = T1.d)
UPDATE T134 T1 SET b = b - (SELECT case when b>c then c else b end FROM T134 T2 WHERE b <> c AND a = '010400' AND T2.d = T1.d),
c = c - (SELECT case when b>c then c else b end FROM T134 T3 WHERE b <> c AND a = '010400' AND T3.d = T1.d)
WHERE (a = '000000' OR a = '010000' or a='010400') AND
EXISTS(SELECT 1 FROM T134 T4 WHERE b <> c AND a = '010400' AND T4.d = T1.d)