存储过程--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')