mysql:用户自定义变量关联失效

 

 

 

SET @applykey = 0, @loanRank = 0;

SELECT applyId, custId, ruleVersion, rejectRule, STATUS
    , extra, createTime, updateTime, mobile
    , IF(STATUS = 1, IF(@applykey <> custId, @loanRank := 1, @loanRank := @loanRank + 1), '0') AS applyPassRank
    , @applykey := custId
FROM (
    SELECT applyId, custId, ruleVersion, rejectRule, STATUS
        , extra, createTime, updateTime, mobile
    FROM sync.credit_apply
    WHERE updateTime < '2019-11-27'
    ORDER BY custId, updateTime
) t;

SET @cfkkey = 0, @cfkRank = 0;

SELECT cust_id, loan_amt_quota
    , IF(@cfkkey <> cust_id, @cfkRank := 1, @cfkRank := @cfkRank + 1) AS cfkRank
    , @cfkkey := cust_id
FROM (
    SELECT cust_id, loan_amt AS loan_amt_quota
    FROM sync.loandb_cfk_loan
    ORDER BY cust_id, create_time
) t

SET @applykey = 0, @loanRank = 0;
SET @cfkkey = 0, @cfkRank = 0;

CREATE TABLE test2
AS
SELECT applyId, custId, ruleVersion, rejectRule, STATUS
    , extra, loan_amt_quota
    , IF(first_finish_loan_Time IS NULL, 1, IF(updateTime < first_finish_loan_Time, 1, 0)) AS is_new
    , createTime, updateTime, mobile, applyPassRank, cfk_Rank
FROM (
    SELECT applyId, custId, ruleVersion, rejectRule, STATUS
        , extra, createTime, updateTime, mobile
        , IF(STATUS = 1, IF(@applykey <> custId, @loanRank := 1, @loanRank := @loanRank + 1), '0') AS applyPassRank
        , @applykey := custId
    FROM (
        SELECT applyId, custId, ruleVersion, rejectRule, STATUS
            , extra, createTime, updateTime, mobile
        FROM sync.credit_apply
        WHERE updateTime < '2019-11-27'
        ORDER BY custId, updateTime
    ) t
) apply
    LEFT JOIN (
        SELECT cust_id
            , CASE
                WHEN locate('|', updateTime) > 0 THEN SUBSTR(updateTime, 1, INSTR(updateTime, '|') - 1)
                ELSE updateTime
            END AS first_finish_loan_Time
        FROM (
            SELECT cust_id, GROUP_CONCAT(update_Time ORDER BY update_Time ASC SEPARATOR '|') AS updateTime
            FROM sync.loandb_cfk_loan
            WHERE (substr(update_Time, 1, 10) < '2019-11-27'
                AND loan_type = 'xxx'
                AND loan_status = 'yyy')
            GROUP BY cust_id
        ) t
    ) cfk_loan
    ON apply.custId = cfk_loan.cust_id
    LEFT JOIN (
        SELECT cust_id, loan_amt_quota
            , IF(@cfkkey <> cust_id, @cfkRank := 1, @cfkRank := @cfkRank + 1) AS cfk_Rank
            , @cfkkey := cust_id
        FROM (
            SELECT cust_id, loan_amt AS loan_amt_quota
            FROM sync.loandb_cfk_loan
            ORDER BY cust_id, create_time
        ) t
    ) quota
    ON apply.custId = quota.cust_id
        AND apply.applyPassRank = quota.cfk_Rank;