我有一个这样的表:

+-----------+------------+---------------+-------------+--------------+
| member_id |  balance   |     amount    | new_balance |     time     |
+-----------+------------+---------------+-------------+--------------+
|         5 |     630    |      -30      |     600     |  2017-08-14  |
|         3 |     142    |      -68      |      74     |  2017-08-14  |
|         3 |     120    |       22      |     142     |  2017-08-13  |
|         3 |      0     |      120      |     120     |  2017-08-12  |
|        20 |     300    |      324      |     624     |  2017-08-12  |
|         4 |     100    |      -30      |      70     |  2017-08-11  |
|         4 |      0     |      100      |     100     |  2017-08-10  |
|         5 |     30     |      600      |     630     |  2017-08-10  |
+-----------+------------+---------------+-------------+--------------+

我希望选择的是:

+-----------+------------+---------------+---------------+-------------+
| member_id |  balance   |   in_amount   |   out_amount  | new_balance |
+-----------+------------+---------------+---------------+-------------+
|         3 |       0    |      142      |        0      |     142     |
|         4 |     100    |        0      |      -30      |      70     |
|         5 |     630    |        0      |        0      |     630     |
|        20 |     300    |      324      |        0      |     624     |
+-----------+------------+---------------+---------------+-------------+

某个时间段内(2017-08-11到2017-08-13)的总结。

余额:期初余额；

in_amount:期间内正数的总和；

out_amount:期间负数的总和；

new_balance:期末余额；

为了获得收入和结果，我已经制定了这样的声明:

set @start = "2017-08-11 00:00:00";
set @end = "2017-08-13 00:00:00";
SELECT 
    DISTINCT(member_id),
    sum(if(amount>0, amount, 0)) as in_amount,
    sum(if(amount<0, amount, 0)) as out_amount,
FROM transaction 
WHERE 
    (time BETWEEN  @start and @end) 
group by member_id;

但是，我不知道将 balance 和 new_balance 包含到语句中。有人对我有什么建议吗？？

最佳答案

使用两个子查询和一个 LEFT JOIN .

第一个子查询检索最近的余额。 第二个子查询使用日期参数汇总每个成员的进出金额。

使用 LEFT JOIN允许我们获得每个成员的余额，包括那些没有在 @start 之间进行交易的成员和 @end

set @start = "2017-08-11 00:00:00";
set @end = "2017-08-13 00:00:00";
select
    lastTransaction.member_id, 
    lastTransaction.balance,
    IFNULL(computeInOut.in_amount, 0) as in_amount, 
    IFNULL(computeInOut.out_amount, 0) as out_amount, 
    lastTransaction.balance, 
    -- balance is the most recent balance + computed in amout - computed out amount
    (lastTransaction.balance + IFNULL(computeInOut.in_amount, 0) + IFNULL(computeInOut.out_amount, 0)) as new_balance
FROM 
    ( -- retrieving the most recent balance information prior to @start
    SELECT 
        member_id,
        max(time) as mostRecentTransaction, 
        balance
    FROM transaction 
    WHERE time <= @start
    group by member_id
     ) as lastTransaction
LEFT JOIN 
    ( -- calculating in & out amounts sum based on the date parameters
    SELECT 
        member_id,
        sum(if(amount>0, amount, 0)) as in_amount,
        sum(if(amount<0, amount, 0)) as out_amount
    FROM transaction 
    WHERE 
        (time BETWEEN  @start and @end) 
    group by member_id
    ) as computeInOut
ON (lastTransaction.member_id = computeInOut.member_id)

结果:

+-----------+---------+--------+---------+------------+
| member_id | balance | inflow | outflow | newbalance |
+-----------+---------+--------+---------+------------+
|         4 |     100 |      0 |     -30 |         70 |
|         5 |       30|      0 |       0 |         30 |
+-----------+---------+--------+---------+------------+

成员 5 和 20 不在结果中，因为他们的第一笔交易发生在@start 之后，所以在事件发生之前没有余额(11h 和 13 之间的交易)

关于MYSQL如何在一条语句中选择总和列值和特定行条目？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/46213969/