mysql - 使用连接在 MySQL 中计算 SUM

Question

背景

在我的数据库中，我有一个表格，里面装满了项目。每个项目都可以有一系列的付款。
一旦用户付款，用户就可以通过兑换来使用这笔钱。钱可以部分兑换。

问题

对于每次赎回，我都想计算剩余的钱。这等于执行 SUM(paid_amount) - SUM(previous_redemptions) - this_redemption。

问题在于，由于 SQL 连接的性质(一行是连接的组合)，如果之前有多次兑换，每笔付款将被计算多次。

我实际上不确定我想要的东西可以仅使用 MySQL 来计算(并且仍然相当快)。

如果我能以某种方式使每个 SQL 组仅包含一行且每列有多个值，问题就会得到解决，我认为 SQL 不支持这一点。

表格

mysql> DESCRIBE items;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

mysql> DESCRIBE payments;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| id          | int(11) | NO   | PRI | NULL    |       |
| item_id     | int(11) | NO   |     | NULL    |       |
| paid_amount | int(11) | NO   |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+

mysql> DESCRIBE redemptions;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| id          | int(11)  | NO   | PRI | NULL    |       |
| item_id     | int(11)  | NO   |     | NULL    |       |
| amount      | int(11)  | YES  |     | NULL    |       |
| create_time | datetime | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+

数据

mysql> SELECT * FROM items;
+----+
| id |
+----+
|  1 |
+----+

mysql> SELECT * FROM payments;
+----+---------+-------------+
| id | item_id | paid_amount |
+----+---------+-------------+
|  1 |       1 |          50 |
|  2 |       1 |          50 |
+----+---------+-------------+

mysql> SELECT * FROM redemptions;
+----+---------+--------+---------------------+
| id | item_id | amount | create_time         |
+----+---------+--------+---------------------+
|  1 |       1 |     10 | 2013-01-01 00:00:00 |
|  2 |       1 |     10 | 2013-01-01 00:01:00 |
|  3 |       1 |     10 | 2013-01-01 00:02:00 |
+----+---------+--------+---------------------+

查询

mysql> SELECT
    ->     redemptions.id AS redemption_id,
    ->     SUM(payments.paid_amount) - COALESCE(SUM(previous_redemptions.amount), 0) - redemptions.amount AS remaining_balance
    -> FROM redemptions
    -> JOIN payments ON payments.item_id = redemptions.item_id
    -> LEFT JOIN redemptions AS previous_redemptions
    -> ON
    ->     previous_redemptions.item_id = redemptions.item_id AND
    ->     previous_redemptions.create_time < redemptions.create_time
    -> GROUP BY redemptions.id;
+---------------+-------------------+
| redemption_id | remaining_balance |
+---------------+-------------------+
|             1 |                90 |
|             2 |                70 |
|             3 |               150 |
+---------------+-------------------+

如您所见，这并没有真正按照我的意愿行事。我希望兑换 3 的 remaining_balance 为 70。

表的大小

• 项目 - 几百万行
• 付款 - 几百万行
• 赎回 - 几百万行

这意味着进行子查询以首先计算每次兑换的所有使用金额(先前兑换的总和)是不可能的。

为那些想在家学习的人准备的 MySQL 命令

CREATE TABLE items (
    id int(11) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE payments (
    id int(11) NOT NULL,
    item_id int(11) NOT NULL,
    paid_amount int(11) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE redemptions (
    id int(11) NOT NULL,
    item_id int(11) NOT NULL,
    amount int(11),
    create_time datetime,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO items VALUES (1);

INSERT INTO payments VALUES
    (1, 1, 50),
    (2, 1, 50);

INSERT INTO redemptions VALUES
    (1, 1, 10, '2013-01-01 00:00:00'),
    (2, 1, 10, '2013-01-01 00:01:00'),
    (3, 1, 10, '2013-01-01 00:02:00');

SELECT 
    redemptions.id AS redemption_id,
    SUM(payments.paid_amount) - COALESCE(SUM(previous_redemptions.amount), 0) - redemptions.amount AS remaining_balance
FROM redemptions
JOIN payments ON payments.item_id = redemptions.item_id
LEFT JOIN redemptions AS previous_redemptions
ON
    previous_redemptions.item_id = redemptions.item_id AND 
    previous_redemptions.create_time < redemptions.create_time
GROUP BY redemptions.id;

Answer 1

给你!

SELECT 
    redemptions.id AS redemption_id,
    (payments.paid_amount - SUM(previous_redemptions.amount)) AS remaining_balance
FROM redemptions
JOIN payments ON payments.id = redemptions.payment_id
LEFT JOIN redemptions AS previous_redemptions
ON
    previous_redemptions.payment_id = redemptions.payment_id AND 
    previous_redemptions.id <= redemptions.id
GROUP BY redemptions.id;

SQL Fiddle