最初由 shipatrioc 发布
[B]table_3:
no --data1 --data2
2 --200 --300
3 --300--------
5 -----------500 [/B]
DROP TABLE T_A
Table dropped
DROP TABLE T_B
Table dropped
DROP TABLE T_C
Table dropped
CREATE TABLE T_A AS
SELECT ROWNUM NO, ROWNUM DATA FROM DUAL
Table created
CREATE TABLE T_B AS
SELECT * FROM T_A
Table created
CREATE TABLE T_C AS
SELECT ROWNUM NO, ROWNUM DATA_1, ROWNUM DATA_2 FROM DUAL
Table created
BEGIN
FOR X IN 1..10
LOOP
INSERT INTO T_A
VALUES(X,X);
END LOOP;
COMMIT;
END;
PL/SQL procedure successfully completed
BEGIN
FOR X IN 5..15
LOOP
INSERT INTO T_B
VALUES(X,X);
END LOOP;
COMMIT;
END;
PL/SQL procedure successfully completed
SELECT * FROM T_A
NO DATA
---------- ----------
1
1
1
1
2
2
3
3
4
4
5
5
6
6
7
7
8
8
9
9
10 10
11 rows selected
SELECT * FROM T_B
NO DATA
---------- ----------
1
1
5
5
6
6
7
7
8
8
9
9
10 10
11 11
12 12
13 13
14 14
15 15
12 rows selected
TRUNCATE TABLET_C
Table truncated
[B]
INSERT INTO T_C
SELECT A.NO,(SELECT SUM(DATA) FROM T_A WHERE NO= A.NO),(SELECT SUM(DATA) FROM T_B WHERE NO= A.NO)
FROM (SELECT NO FROM T_A UNION SELECT NO FROM T_B) A
[/B]
15 rows inserted
SELECT * FROM T_C
NO DATA_1 DATA_2
---------- ---------- ----------
1
2
1
2
2
3
3
4
4
5
5
5
6
6
6
7
7
7
8
8
8
9
9
9
10 10 10
11
11
12
12
13
13
14
14
15
15
15 rows selected
[RED]A[/RED]
|
