表结构与数据如下:
CREATE TABLE TLAG(ID NUMBER,A NUMBER, B NUMBER,C NUMBER);
INSERT INTO TLAG VALUES(1,4,1,NULL);
INSERT INTO TLAG VALUES(2,3,5,NULL);
INSERT INTO TLAG VALUES(3,9,3,NULL);
INSERT INTO TLAG VALUES(4,0,2,NULL);
INSERT INTO TLAG VALUES(5,10,0,NULL);
COMMIT;
CREATE TABLE TLAG(ID NUMBER,A NUMBER, B NUMBER,C NUMBER);
INSERT INTO TLAG VALUES(1,4,1,NULL);
INSERT INTO TLAG VALUES(2,3,5,NULL);
INSERT INTO TLAG VALUES(3,9,3,NULL);
INSERT INTO TLAG VALUES(4,0,2,NULL);
INSERT INTO TLAG VALUES(5,10,0,NULL);
COMMIT;
SELECT * FROM TLAG;
ID A B C
1 4 1
2 3 5
3 9 3
4 0 2
5 10 0
ID A B C
1 4 1
2 3 5
3 9 3
4 0 2
5 10 0
需求:更新C列的值,规则如下:
ID A B C
1 4 1 4-1=3
2 3 5 3-5+3=1
3 9 3 9-3+1=7
4 0 2 0-2+7=5
5 10 0 10-2+5=15
从第二行的C值都得加上上一行所求的值。
该如何进行更新呢?请指点。
我的解法如下:
SQL> merge INTO TLAG t USING
2 (SELECT t.id,
3 t.A,
4 t.B,
5 SUM(t.A-t.B) over(order by t.id rows BETWEEN unbounded preceding AND CURRENT row) A_B
6 FROM tlag t
7 GROUP BY t.id,
8 t.A,
9 t.B
10 ) t1 ON (t.id=t1.id)
11 WHEN matched THEN
12 UPDATE SET t.C=t1.A_B WHERE t.id=t1.id
13 /
2 (SELECT t.id,
3 t.A,
4 t.B,
5 SUM(t.A-t.B) over(order by t.id rows BETWEEN unbounded preceding AND CURRENT row) A_B
6 FROM tlag t
7 GROUP BY t.id,
8 t.A,
9 t.B
10 ) t1 ON (t.id=t1.id)
11 WHEN matched THEN
12 UPDATE SET t.C=t1.A_B WHERE t.id=t1.id
13 /
5 行已合并。
SQL> SELECT * FROM TLAG;
ID A B C
---------- ---------- ---------- ----------
1 4 1 3
2 3 5 1
3 9 3 7
4 0 2 5
5 10 0 15
---------- ---------- ---------- ----------
1 4 1 3
2 3 5 1
3 9 3 7
4 0 2 5
5 10 0 15
SQL>
点评:这题巧妙运用了merge和分析函数中的窗口函数!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26977915/viewspace-733836/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26977915/viewspace-733836/