sql oracle

1、修改字段属性

alter table S_LOG_RECORD  modify VC_KEY_WORD  varchar(200);

2、first_value使用

(1)select distinct first_value(t.f_rel_id) over(order by t.f_rel_id desc)  from e_emp_fundhold t  --如果不写partition 只取一条并且是相同的

(2)select first_value(t.vc_fundcode) over(partition by t.vc_fundcode, t.vc_fundname order by t.d_update_time desc),
                first_value(t.vc_fundname) over(partition by t.vc_fundcode, t.vc_fundname order by t.d_update_time desc)
  from e_emp_fundhold t; --如果使用partition 可以根据填写的分组

3、MERGE 使用方法

 MERGE INTO
       e_securities_confirm r
       USING ( SELECT  #c_id# as c_id ,#sid# as sid , #dealDate# as dealDate , #dealPrice# as dealPrice , #dealAmount# as dealAmount , #dealMoney# as dealMoney , #confirmDate# as confirmDate FROM dual ) m
       ON (r.vc_id=m.c_id and r.F_SCR_ID=m.sid and r.d_deal_date=m.dealDate and r.f_deal_price = dealPrice and r.f_deal_amount=dealAmount and r.f_deal_money = dealMoney and r.d_confirm_date = confirmDate)
       when MATCHED THEN
       update set r.vc_flag = '1'
       WHEN NOT MATCHED THEN
       insert
       (F_SC_ID, F_SCR_ID, D_DEAL_DATE, F_DEAL_PRICE, F_DEAL_AMOUNT, F_DEAL_MONEY,D_CONFIRM_DATE,VC_ID)
       values
       (SEQ_SECURITIES_CONFIRM_F_SC_ID.NEXTVAL, m.sid, m.dealDate,m.dealPrice,m.dealAmount,m.dealMoney,m.confirmDate,m.c_id)

4、sum的使用

select distinct
      s.f_emp_id empId,
      s.f_rel_id relId,
      s.vc_scr_code stockCode,
      s.vc_scr_name stockName,
      v.vc_name empName,
      v.vc_user_name empUserName,
      to_char(es.d_deal_date,'yyyy-MM-dd') realDate,
      decode(s.vc_bsflag, 'B', '买入', 'S', '卖出','K', '开仓', 'P', '平仓') direction,
      nvl(e.vc_rel_name, v.vc_name) relName,
      decode(e.vc_relation, '1', '父亲', '2', '母亲','3', '配偶', '4','儿子','5', '女儿', '6','其他利害关系人', '本人') relation,
      to_char(nvl(sum(es.f_deal_money) over(partition by s.vc_scr_code,v.vc_user_name,nvl(e.vc_rel_name, v.vc_name),es.d_deal_date),0),'fm999,999,990.00') nowDaymoney,
      to_char(f.d_date,'yyyy-MM-dd') fundDate,
      f.vc_fundcode fundCode,
      m.vc_fundname fundName,
      to_char(sum(f.f_money) over(partition by f.vc_fundcode,f.d_date),'fm999,999,990.00') mangerMoney
 from E_SECURITIES s
 left join sys_user v
   on s.f_emp_id = v.l_user_id
 left join e_employee_relatlive e
   on s.f_rel_id = e.f_rel_id
 left join E_SECURITIES_CONFIRM es
   on s.f_scr_id = es.f_scr_id  
 left join f_trade_info f
   on f.d_date = es.d_deal_date and f.vc_stockcode = vc_scr_code
 left join e_fundinfo m
   on f.vc_fundcode = m.vc_fundcode   
where s.vc_deal_confirm = 1 and s.vc_bsflag = 'S' and s.vc_stock_type = 'E'

5、dblink的使用

select * from ODS_USER
select * from ODS_DEPARTMENT
select * from ODS_POSITION
select * from ODS_TRADEACCOUNT_TA
select * from Ods_Customerinfo_Ta


select * from ODS_TRADECONFIRM_TA
--dblink
create database link re_tradedb_hr connect to hrdb identified by hrdb using 'orcl_21'; 
create database link re_tradedb_ta connect to tadb identified by tadb using 'orcl_21';

create database link TADB connect to re_trade_online identified by re_trade_online using '192.168.1.100/orcl';
drop database link re_tradedb_hr;
--同义词
create synonym HRMRESOURCE for hrdb.HRMRESOURCE@re_tradedb_hr;
create synonym HRMJOBTITLES for hrdb.HRMJOBTITLES@re_tradedb_hr;
create synonym HRMDEPARTMENT for hrdb.HRMDEPARTMENT@re_tradedb_hr;
create synonym DC_CUSTOMERINFO for tadb.DC_CUSTOMERINFO@re_tradedb_ta;
create synonym DC_FUNDINFO for tadb.DC_FUNDINFO@re_tradedb_ta;
create synonym DC_SHARE_HISTORY for tadb.DC_SHARE_HISTORY@re_tradedb_ta;
create synonym DC_TRADECONFIRM for tadb.DC_TRADECONFIRM@re_tradedb_ta;
select * from DC_CUSTOMERINFO@re_tradedb_ta

--统计sql
            SELECT b.f_emp_id f_emp_id, b.f_rel_id, b.vc_stockcode, b.vc_stockname,  b.vc_exchange,  b.pcount, b.f_amount,a.vc_status FROM
            (
            SELECT e.f_emp_id, e.f_rel_id, e.vc_stockcode, e.vc_stockname,  e.vc_exchange, 0 AS pcount, e.f_amount FROM e_emp_stockhold e 
            WHERE e.vc_year = '2014' AND e.vc_quarter = '2' AND e.vc_stocktype = 'E'
            UNION ALL
            SELECT MAX(e.f_emp_id) + 1, -1, e. vc_stockcode, '0', '0',  COUNT(distinct e.f_rel_id), SUM(e.f_amount) FROM e_emp_stockhold e
            WHERE e.vc_year = '2014' AND e.vc_quarter = '2' AND e.vc_stocktype = 'E' 
            GROUP BY  e.vc_stockcode
            
            UNION ALL
            SELECT MAX(e.f_emp_id) + 1,-2, 'Zzzzzz', '0', '0', COUNT(distinct e.f_rel_id), SUM(e.f_amount) FROM e_emp_stockhold e
            WHERE e.vc_year = '2014' AND e.vc_quarter = '2' AND e.vc_stocktype = 'E' 
            ) b left join e_emp_applystate a on a.f_emp_id = b.f_emp_id
            
            
             ORDER BY vc_stockcode,f_emp_id