笔记20130408

#oracle导入命令
imp username/password@orcl file=G:\20130408_clsms.dmp fromuser=clweb toUser=clweb

#INSERT INTO SELECT语句
语句形式为：Insert into Table2(field1,field2,...) select value1,value2,... from Table1

#SELECT INTO FROM语句
语句形式为：SELECT vale1, value2 into Table2 from Table1

select t1.vc_title,t1.vc_title3,t1.vc_lead,t1.Vc_Author,t1.vc_source,t3.b_content,
'/news/'||substr(to_char(t1.d_createtime,'yyyy-mm-dd hh:mi:ss'),1,4)
||'/'||substr(to_char(t1.d_createtime,'yyyy-mm-dd hh:mi:ss'),6,2)
||'/'||substr(to_char(t1.d_createtime,'yyyy-mm-dd hh:mi:ss'),9,2)
||'/' as n_day,
(t1.d_createtime-to_date('1970-01-01','yyyy-mm-dd'))*86400000 as times,
DECODE(t2.vc_name,'国内新闻','1','国外新闻','1','头版新闻','1','理财贴士','7','近期关注热点','4','市场经济观点',
'3','公司公告','9','1') as newstype,
DECODE(t2.vc_name,'国内新闻','101','国外新闻','102','头版新闻','103','理财贴士','701','近期关注热点','401','市场经济观点','304',
'公司公告','901','00') as newssubtype,
t1.d_createtime
from TB_ARTICLE_INFO t1, TB_ARTICLE_COLUMN t2, TB_ARTICLE_CONTENT t3
where t1.i_columnid = t2.i_id and t1.i_articleid = t3.i_articleid
and t3.c_deleted = 0 and t1.c_deleted = 0
and t1.c_articletype = 0 and t1.d_createtime > to_date('2012-12-11 00:00:00','yyyy-mm-dd hh24:mi:ss');