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')