sql笔记

获取第一条记录

• select * from ad_plan limit 0,1

去重sql：

delete from change_of_rate where (reference = 'GDAX' or reference ='BSTP' or reference='KRAK')
and id not in (select a.id from ( select min(id) as id FROM change_of_rate GROUP BY reference,create_time
HAVING count(*) > 1) a)

存储过程

• clearing_proc

CREATE DEFINER=`root`@`%` PROCEDURE `clearing_proc`(IN makerId VARCHAR(255))
BEGIN

DECLARE v_IDFrom VARCHAR(255);
DECLARE v_cashFlow DOUBLE;
DECLARE v_cashFlowCur VARCHAR(255);
DECLARE v_dealID VARCHAR(255);
DECLARE v_clearingID INT;

DECLARE cusmargin_done INT DEFAULT FALSE;
DECLARE cur_cusmargin CURSOR FOR 
    select C.id_from ,C.cash_flow AS CcashFlow,C.cash_flow_currency,C.deal_id,C.clearing_id 
    from clearing_cashflow as C where book_from="Cash_cusmargin" AND 
    C.clearing_id not in (SELECT clearing_id FROM cash_cusmargin);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET cusmargin_done = TRUE;

SET @curTime = DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');

OPEN cur_cusmargin;     
    read_loop1: LOOP
        FETCH cur_cusmargin INTO v_IDFrom, v_cashFlow, v_cashFlowCur, v_dealID, v_clearingID;
            IF cusmargin_done THEN
                LEAVE read_loop1;
            END IF;
            insert into cash_cusmargin(cus_id,log_date_time
            ,cash_flow,cash_flow_currency,status,deal_id,source,clearing_id)
            values(v_IDFrom,@curTime,-1 * v_cashFlow,v_cashFlowCur
            ,"batchClearingToCash",v_dealID,"batchClearingToCash",v_clearingID);

            BEGIN
                DECLARE makercash_done INT DEFAULT FALSE;
                DECLARE cur_makercash CURSOR FOR 
                    select C.cash_flow,C.cash_flow_currency,C.deal_id,C.clearing_id
                    from clearing_cashflow as C where book_to="cash_makercash" AND 
                    C.clearing_id not in (SELECT clearing_id FROM cash_makercash);
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET makercash_done = TRUE;

                OPEN cur_makercash;     
                    read_loop2: LOOP
                        FETCH cur_makercash INTO v_cashFlow, v_cashFlowCur, v_dealID,v_clearingID;
                            IF makercash_done THEN
                                LEAVE read_loop2;
                            END IF;
                        insert into cash_makercash(maker_potfolio_id
                        ,log_date_time,cash_flow,cash_flow_currency,status,deal_id
                        ,source,clearing_id)
                        values(makerId,@curTime,v_cashFlow,v_cashFlowCur
                        ,"batchClearingToCash",v_dealID,"batchClearingToCash",v_clearingID);
                    END LOOP read_loop2;
                CLOSE cur_makercash;
            END;

    END LOOP read_loop1;
CLOSE cur_cusmargin;
END;

• decision_range_proc

CREATE DEFINER=`root`@`%` PROCEDURE `decision_range_proc`(in cutoffDateTime dateTime,in spotRef double(32, 8))
BEGIN

DECLARE v_id VARCHAR(255);
DECLARE v_dealID VARCHAR(255);
DECLARE v_DdealID VARCHAR(255);
DECLARE v_cusID VARCHAR(255);
DECLARE v_notional VARCHAR(255);
DECLARE v_notionalCur VARCHAR(255);
DECLARE d_cashFlow DOUBLE(32, 8) DEFAULT 0;
DECLARE i_profit INT DEFAULT 0;
DECLARE d_balance DOUBLE(32, 8) DEFAULT 0;
DECLARE i_type INT DEFAULT 0;
DECLARE i_total_cash_flow_type INT DEFAULT 0;
DECLARE i_cash_flow_type INT DEFAULT 0;
DECLARE d_total_balance DOUBLE(32, 8) DEFAULT 0;

BEGIN
    update deal_cusspreadcupondeal set status="exe", judge_price=spotRef where expiry_cutoff_date_time=cutoffDateTime and strike_from<=spotRef and strike_to>spotRef and status="live";
    update deal_cusspreadcupondeal set status="unexe", judge_price=spotRef where expiry_cutoff_date_time=cutoffDateTime and status="live";

    update position_option_maker  set status="exe" where expiry=cutoffDateTime and status="live"
            and(( CALL_PUT =1   and Strike<=spotRef ) OR ( CALL_PUT =-1 and Strike>=spotRef));
    update position_option_maker set status="unexe" where expiry=cutoffDateTime and status="live";
END;

BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE deal_cusspreadcupondeal CURSOR FOR 
        select D.id,D.deal_id,CONCAT(D.deal_id,"-",D.ID) as DdealID,D.cus_id,D.notional,D.notional_cur,type,TRUNCATE(D.notional*100/spotRef, 4) as DcashFlow, 100 as profit
        from deal_cusspreadcupondeal as D where expiry_cutoff_date_time=cutoffDateTime and status='exe' AND deal_date < '2018-11-02 18:00:00'
    UNION
    select D.id,D.deal_id,CONCAT(D.deal_id,"-",D.ID) as DdealID,D.cus_id,D.notional,D.notional_cur,type,TRUNCATE(D.notional*10/spotRef, 4) as DcashFlow, 10 as profit
        from deal_cusspreadcupondeal as D where expiry_cutoff_date_time=cutoffDateTime and status='exe' AND deal_date > '2018-11-02 18:00:00';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 打开游标
    OPEN deal_cusspreadcupondeal;   
    -- 遍历
        read_loop: LOOP
                -- 取值 取多个字段
                FETCH deal_cusspreadcupondeal INTO v_id, v_dealID, v_DdealID, v_cusID, v_notional, v_notionalCur, i_type, d_cashFlow, i_profit;
                IF done THEN
                    LEAVE read_loop;
                END IF; 
                
                insert into clearing_cashflow(cash_flow,cash_flow_currency,book_from,id_from,book_to,id_to,deal_id,log_date_time,source)
                values(-1 * d_cashFlow,v_notionalCur,"Cash_cusMargin",v_cusID,"Cash_makercash","makerCS",v_dealID,
                DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'),"batchJudgment");


                IF i_type = 0 THEN
                    set i_cash_flow_type = 6;
                ELSEIF i_type = 1 THEN
                    set i_cash_flow_type = 7;
                ELSE
                    set i_cash_flow_type = 8;
                END IF;
                UPDATE deal_cusspreadcupondeal SET profit = i_profit WHERE deal_id = v_dealID;
                UPDATE customer SET balance = balance + d_cashFlow WHERE cus_id = v_cusID;
                SELECT @d_balance:=balance FROM customer WHERE cus_id = v_cusID;
--              SELECT @d_balance:=balance FROM cash_flow ORDER BY created DESC LIMIT 1;
                INSERT INTO cash_flow(cus_id, number, type, asset, amount, balance, created, status)
                VALUES(v_cusID, v_dealID, i_cash_flow_type, "BTC", d_cashFlow, @d_balance, DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'), 0);

                IF i_type = 0 THEN
                    set i_total_cash_flow_type = 11;
                ELSEIF i_type = 1 THEN
                    set i_total_cash_flow_type = 12;
                ELSE
                    set i_total_cash_flow_type = 13;
                END IF;
                SELECT @d_total_balance:=balance FROM total_cash_flow ORDER BY id DESC LIMIT 1;
                INSERT INTO total_cash_flow(cus_id, type, number, relevance, pay_type, asset, amount, balance, remark, channel, created)
                VALUES("平台", i_total_cash_flow_type, v_dealID, v_dealID, 0, "BTC", (-1)*d_cashFlow, @d_total_balance + (-1)*d_cashFlow, "发放奖励", "余额", DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'));
                INSERT INTO total_cash_flow(cus_id, type, number, relevance, pay_type, asset, amount, balance, remark, channel, created)
                VALUES(v_cusID, i_total_cash_flow_type, v_dealID, v_dealID, 1, "BTC", d_cashFlow, @d_total_balance, "发放奖励", "余额", DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'));

        END LOOP read_loop;
    CLOSE deal_cusspreadcupondeal;
END;

END;