MySQL查询日期范围交易语句

Question

我有一个交易表。

    id  account_id  trx_date    trx_type  amount  remarks  
------  ----------  ----------  --------  ------  ---------
     1           1  2017-12-10  DEPOSIT      500  test
     2           1  2017-12-11  DEPOSIT      500  test     
     3           1  2017-12-12  DEPOSIT     6000  test     
     4           1  2017-12-13  WITHDRAW     300  test     
     5           1  2017-12-13  DEPOSIT      200  test

我想得到这种格式的结果，但我无法弄清楚获取数据的查询将如何进行，如下所示。这里的所有字段都与 transactions 表相同。 op_bal、cl_bal为动态字段。

date            trx_type        op_bal      amount      cl_bal
2017-12-12      DEPOSIT         1000        6000            7000
2017-12-13      WITHDRAW        7000        300             6700
2017-12-13      DEPOSIT         6700        200             6900

Answer 1

为我创建的给定输出打招呼 this sqlfiddle请检查答案

CREATE TABLE transactions (
    id bigint(12) PRIMARY KEY auto_increment,
    account_id bigint(12),
    trx_date date,
    trx_type enum('DEPOSIT','WITHDRAW'),
    amount float(10,2),
    remarks varchar(50)
);

INSERT INTO transactions (account_id, trx_date, trx_type, amount, remarks)
VALUES ('1','2017-12-10','DEPOSIT',500,'test'),
       ('1','2017-12-11','DEPOSIT',500,'test'),
       ('1','2017-12-12','DEPOSIT',6000,'test'),
       ('1','2017-12-13','WITHDRAW',300,'test'),
       ('1','2017-12-13','DEPOSIT',200,'test');

查询得到的结果是

SELECT 
trx_date, 
trx_type, 

(SELECT SUM(CASE tr.trx_type WHEN 'DEPOSIT' THEN CONCAT('+',tr.amount) ELSE CONCAT('-', tr.amount) END) as amount FROM transactions as tr WHERE tr.id < (SELECT MAX(t.id) FROM transactions as t WHERE t.id <= transactions.id LIMIT 1) ) as op_bal,
amount AS trx_amount,
(SELECT SUM(CASE tr.trx_type WHEN 'DEPOSIT' THEN CONCAT('+',tr.amount) ELSE CONCAT('-', tr.amount) END) as amount FROM transactions as tr WHERE tr.id <= (SELECT MAX(t.id) FROM transactions as t WHERE t.id <= transactions.id LIMIT 1) ) as cl_bal
FROM transactions 
WHERE DATE(trx_date) BETWEEN '2017-12-12' AND '2017-12-13'

希望它对你有用。