存储过程--InOut

1. 存储过程名称：p_t_tradinglist

2. 参数 

 

3. 过程：

   

BEGIN

  #Routine body goes here...
  set @row=0;

  select 
   @row:=@row+1 as '序号',
   DATE_FORMAT(a.llcurtime,'%Y-%m-%d %T') as '成交时间', if (b.exchange='SHFE','上期所', 
   if(b.exchange='DCE', '大商所', if(b.exchange='CZCE', '郑商所', 'XXX'))) as '交易所',
    b.goodname as '商品', a.contractid as '合约', if(a.direct='50','买', if(a.direct='51','卖','XXX')) as '买/卖',
    a.price as '成交价', sum(a.share) as '手数',
   if(a.flag=48,'开仓',(if(a.flag='51','平今', if(a.flag='52','平昨','XXX')))) as '开/平', -a.commission as '手续费',
    a.orderid, d.price1 as '开仓价', a.price as '平仓价', c.lastPrices  as '结算价', e.vol_muliple,a.flag,   
   if(a.flag='48','', if(a.flag='51',(sum(a.share*a.price)/sum(a.share)-d.price1),(sum(a.share*a.price)/sum(a.share)-c.lastPrices))*if(direct='50',-1,1)*a.share*e.vol_muliple) as '平仓盈亏'
   #,a.innerorderid
   from hd_position_operator a 
     left JOIN hdmarket_monitor.t_good_instrument b on a.contractid = b.instrumentid 
     left join (select open_day, last_day from hd_trading_date)f on f.open_day = a.lltradedate
     LEFT JOIN (select open_day, instrument, lastPrices from hd_settle_price)c 
            on b.instrumentid = c.instrument and c.open_day = f.last_day
     LEFT JOIN (select orderid, sum(price*share)/sum(share) as price1 from hd_position_operator where flag = '48' and errorcode = '64'
                group by orderid)d on a.orderid = d.orderid
     left join (select contractid, vol_muliple from hd_contract_parm group by contractid)e on a.contractid = e.contractid
   where a.serid like i_serid and a.lltradedate = i_tradingdate and a.errorcode = '64' group by a.innerorderid 
   order by a.llcurtime; 

END

 

4. 执行过程：

   call p_t_tradinglist('%110112%','20180530')