2017.8.17实操总结

1.insert into select from

从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。

例：

insert into fact.T_FACT_USER_BASE_label
select /*+parallel(8)*/
to_char(add_months(sysdate-1,-1),'mm'),
to_char(add_months(sysdate-1,-1),'yyyymm'),
user_id,
serial_number,
cust_name,
null,null,null,null,null,null,null,null,null,null,null,
case when depart_kind_name='集客渠道' and length(cust_name)>5 then 1 else 0 end,
case when group_id is not null then 1 else 0 end,
0,0,0,null,null,null,null,
use_brandname,
use_sunbrandname,
substr(property,6,1),
null,null,null,
null,null,null,null,null,
state_name,
round(case when is_innet=1 then months_between(sysdate-1,in_date) else months_between(nvl(destroy_time,last_stop_time),in_date) end,2),
0,0,0,0,null,
action_name,is_szt,case when tuoshou_flag=1 then 1 else 0 end,
brand_type,brand_kind,product_name,
CASE WHEN PRODUCT_NAME LIKE '%员工%' then 1 else 0 end,
case when cust_manager_team is not null then 1 else 0 end,
case when cust_manager_team is not null then cust_level else '' end
from fact.t_fact_userinfo_allnet where monpart=to_char(add_months(sysdate-1,-1),'mm')
and  net_type_code in ('10','50','15','16','13') ;
commit;

 

2.merge into....

格式

MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)
WHEN MATCHED THEN
    [UPDATE sql]
WHEN NOT MATCHED THEN
    [INSERT sql]

 判断Ｂ表和Ａ表是否满足ON中条件，如果满足则用B表去更新A表，如果不满足，则将B表数据插入A表但是有很多可选项

例： merge into FACT.T_FACT_USER_BASE_LABEL a using t_user_idcard_info_4g b on (a.user_id=b.user_id and   b.籍贯 is not null and substr(certnum,1,5)='44030' and a.monpart=to_char(add_months(sysdate-1,-1),'mm') and a.native_place is not null) when matched then update set   a.native_place_type ='深圳市'; commit;

3.create A as select..

1. create table table1 as select  * from table2  where 1=2; 创建一个表结构与table2一模一样的表，只复制结构不复制数据； 2.create  table table1  as  select *   from table2  ;   创建一个表结构与table2一模一样的表,复制结构同时也复制数据； 3.create  table table1(columns_a,columns_b) as select  columns1,columns2  from table2;  创建一个表结构与table2一模一样的表,复制结构同时也复制数据,但是指定新表的列名，这种格式也用于复制table2部分字段；

例：

create table t_qianzhuang4g as select a.user_id_cbss,a.user_id_bss,a.accept_date_cbss accept_date,b.in_date, case when brand_code not in ('11','12','13','14','15','16','17','18') and net_type_code in ('10','13') then '3G' else '2G' end brand_code from tf_f_user_to4g_all@link_szdb a,ods.tf_f_user b where a.user_id_bss=b.user_id