先更新a字段010000 的 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;
实测数据CREATE TABLE T134 ( 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'; 实测结果:
恩,谢谢楼上两位,第一个回答的会产生NULL数据,第二很好,谢谢。 还有个问题: 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)
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)