SQL> create
table abcd(po_num number,release_num number,transacton_type varchar2(20),quantity number);
表已创建。
SQL> insert into abcd values(19,1,'RECEIVE',5);
已创建 1 行。
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT ptimizer=CHOOSE
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to
client 0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SET autotrace off;
SQL> insert into abcd values(19,1,'ACCEPT',5);
已创建 1 行。
SQL> insert into abcd values(19,2,'ACCEPT',2);
已创建 1 行。
SQL> insert into abcd values(19,2,'RECEIVE',2);
已创建 1 行。
SQL> insert into abcd values(19,2,'DELIVER',2);
已创建 1 行。
SQL> insert into abcd values(19,3,'TRANSFER',2);
已创建 1 行。
SQL> insert into abcd values(19,3,'REJECT',2);
已创建 1 行。
SQL> insert into abcd values(19,3,'RECEIVE',11);
已创建 1 行。
SQL> insert into abcd values(19,3,'ACCEPT',9);
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> select po_num,release_num,MAX(DECODE(TRANSACTON_TYPE,'RECEIVE',quantity,0)) c1,
2 MAX(DECODE(TRANSACTON_TYPE,'ACCEPT',quantity,0)) c2,
3 MAX(DECODE(TRANSACTON_TYPE,'REJECT',quantity,0)) c3 From abcd Group By po_num,release_num;
PO_NUM RELEASE_NUM C1 C2 C3
---------- ----------- ---------- ---------- ----------
19 1 5 5 0
19 2 2 2 0
19 3 11 9 2
SQL>
