mysql - 仅返回基于金额的行数的查询

Question

我有一个应付账款表

场景:

我们将向我们的供应商支付 6000，这笔付款适用于第 2 行，剩余的 2500 适用于第 3 行。应用第三行的2500后，余额为1000。

现在我想编写一个查询，根据付款输入返回行数。查询条件如下:

1. 查询余额大于0
2. 仅返回付款适用的行。 即在上面的场景中，付款是 6000，然后查询检查哪些 6000 适用，根据该决定它应该返回行。在 6000 的情况下，他应该返回第 2 行和第 3 行，如果 payment = 8000 则查询将返回 3 行，因为 7000 满足/清除第 2 和第 3 应付账款，剩余的 1000 将减少第 4 个条目的余额。

我想要一个只返回应用付款的行数的查询？

更新我!

Answer 1

考虑到在 duedate 之前订购的请求，并且要知道“剩余”多少，您会得到如下所示的内容。此查询将为您提供 left 中的付款剩余金额以及 new_balance 中减去(剩余的)付款后的余额。

SELECT p.*,
    IF(balance < @left, 0, balance - @left) AS 'new_balance',
    @left := IF(balance > @left, 0, @left - balance) AS 'left'
  FROM (
    SELECT * FROM payable
      WHERE balance > 0
      ORDER BY duedate, payableid
    ) AS p
  JOIN (SELECT @left := 6000) AS r ON @left > 0

上面的查询在SQL Fiddle

一些注意事项:

• 由于 duedate 不是唯一的，我也添加了 payableid(我假设 是 是唯一的)。为了保持一致性，p 的子查询中的 ORDER BY 必须 是唯一的。
• 子查询返回的结果必须仅包含可以记入金额的记录。
  (因此，如果您有一个 account_id 列或类似列，您可以将其包含在子查询的 WHERE 中)。
• 为了优化，子查询返回的结果应该尽可能小
  (因此我们将 WHERE balance > 0 放在子查询中，不放在外部查询中)。
• 如果您想知道“为什么要使用子查询？”:因为 ORDER BY 在选择 之后执行。因此，在我们不使用子查询的情况下，@left 将被错误地应用并且 ORDER BY 将变得无用。