mysql：根据某些条件筛选出来的纪录之间两两作差

需求：存在表Balance_Detail，BALANCE为余额，CHANGE_BALANCE为每次上传账户余额时的余额变化，现要校验CHANGE_BALANCE是否正确

 

语句：

SELECT
    ID,
    ACCOUNT_NUM,
    BALANCE,
    REPORT_DATE,
    CHANGE_BALANCE,
    ifnull(
        a.BALANCE - ( SELECT BALANCE FROM Balance_Detail WHERE ACCOUNT_NUM = a.ACCOUNT_NUM AND REPORT_DATE < a.REPORT_DATE ORDER BY REPORT_DATE DESC LIMIT 1 ),
        0 
    ) AS CHANGE_BALANCE_DIF 
FROM
    Balance_Detail a 
WHERE
    a.ACCOUNT_NUM = '76290078801100000486';

执行结果：