假如一个在线电子商务系统,我们现在需要根据订单表体现的消费金额将客户简单分为大中小三类并分别插入到三张表中.
- insert first
- when tot_amount < 10000 then
- into small_cust_test
- when tot_amount >=10000 and tot_amount <50000 then
- into med_cust_test
- else
- into big_cust_test
- select cust_id,sum(amount) as tot_amount
- from order_test
- group by cust_id;
- FIRST:表示第一WHEN条件符合后就跳到下条记录,不再判断其它WHEN条件。
- ALL :表示不管前面的WHEN条件是否已经满足,后续的条件都会被判断,可能会一次出现多表同时插入。
- SQL> create table order_test (order_id number, cust_id number, amount number);
- Table created
- SQL> create table small_cust_test (cust_id number, tot_amt number);
- Table created
- SQL> create table med_cust_test (cust_id number, tot_amt number);
- Table created
- SQL> create table big_cust_test (cust_id number, tot_amt number);
- Table created
- SQL> select * from order_test order by order_id;
- ORDER_ID CUST_ID AMOUNT
- ---------- ---------- ----------
- 1 1001 2060
- 2 1002 20060
- 3 1003 30060
- 4 1004 50060
- 5 1004 10060
- 6 1005 100060
- 7 1001 2000
- 8 1001 2050
- 8 rows selected
- SQL> select cust_id, sum(amount) as tot_amt from order_test group by cust_id;
- CUST_ID TOT_AMT
- ---------- ----------
- 1003 30060
- 1001 6110
- 1002 20060
- 1004 60120
- 1005 100060
- SQL> select * from small_cust_test;
- CUST_ID TOT_AMT
- ---------- ----------
- SQL> select * from med_cust_test;
- CUST_ID TOT_AMT
- ---------- ----------
- SQL> select * from big_cust_test;
- CUST_ID TOT_AMT
- ---------- ----------
- SQL> insert first
- 2 when tot_amount < 10000 then
- 3 into small_cust_test
- 4 when tot_amount >=10000 and tot_amount <50000 then
- 5 into med_cust_test
- 6 else
- 7 into big_cust_test
- 8 select cust_id,sum(amount) as tot_amount
- 9 from order_test
- 10 group by cust_id;
- 5 rows inserted
- SQL> select * from small_cust_test;
- CUST_ID TOT_AMT
- ---------- ----------
- 1001 6110
- SQL> select * from med_cust_test;
- CUST_ID TOT_AMT
- ---------- ----------
- 1003 30060
- 1002 20060
- SQL> select * from big_cust_test;
- CUST_ID TOT_AMT
- ---------- ----------
- 1004 60120
- 1005 100060
- SQL>