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

  • 相关阅读:
    JavaScript underscore
    JavaScript jQuery 事件、动画、扩展
    JavaScript jQuery 入门回顾
    JavaScript H5 Canvas
    JavaScript Ajax + Promise
    JavaScript 浏览器对象
    JavaScript 面向对象编程
    JavaScript 标准对象
    JavaScript 高阶函数 + generator生成器
    “耐撕”团队2016.04.12站立会议
  • 原文地址:https://www.cnblogs.com/bruce-he/p/9134686.html
Copyright © 2020-2023  润新知