有一张余额表，写一条sql，实时计算每一次收付金额时的余额

假设有一张表：

 CREATE TABLE `money_test` (
  `transaction_id` int(11) NOT NULL COMMENT '主键',
  `transaction_type` varchar(255) DEFAULT NULL COMMENT '收支方向(1：收入，2：支出)',
  `amount` varchar(255) DEFAULT NULL COMMENT '当前余额',
  `transaction_date` datetime DEFAULT NULL COMMENT '交易日期',
  PRIMARY KEY (`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 实时计算每一次收付之后的金额：可以用下列sql来实现

SELECT 
    transaction_id,
    transaction_type,
    amount,
    transaction_date,
    @balance := @balance + (CASE 
                                WHEN transaction_type = '1' THEN amount
                                ELSE -amount
                        END) AS balance
FROM 
    test_table
CROSS JOIN 
    (SELECT @balance := 0) AS init
ORDER BY 
    transaction_date, transaction_id;

上面可能会有精度的缺失，用下面的这个更好

SELECT 
    receipt_pay_date,
    create_date,
    receipt_money,
    pay_money,
    CAST(@balance := @balance + (CASE 
								WHEN receipt_pay_type = '10' THEN receipt_money
								ELSE -pay_money
						END) AS DECIMAL(18, 6)) AS balance
FROM 
    test_table
CROSS JOIN 
    (SELECT @balance := 0) AS init
ORDER BY 
    receipt_pay_date, create_date