mysql - 在多对多中查找最近加入的记录

Question

运行 SQL 查询，提取包括最近交易在内的发票列表。

要求:

• 使用 1 个查询获取此信息(可能需要内部查询)
• 包括尚未进行交易的发票
• 能够按发票或交易中的字段进行过滤。以下示例场景:
  • 示例 1:最后交易状态为已结算的所有发票
  • 示例 2:所有状态为 unpaid 且没有最后一笔交易的发票)

棘手的部分是我们正在处理多对多场景。我发现的所有 StackOverflow 帖子都是关于一对多场景的。以下 SO 帖子似乎将我带向了正确的方向，但无法安静地弄清楚:SQL join: selecting the last records in a one-to-many relationship

我在这里使用多对多的原因是因为交易可以是许多发票的一部分。基本上可以将多张发票分配给同一付款资料，因此我们创建一个交易(而不是为每张发票创建单独的交易)。此外，一张发票可以有很多交易，因为交易可以被拒绝。

我知道您可能会想，为什么不使用总体发票，然后将当前发票设置为 invoice_lines 或类似的东西，但不幸的是，目前无法更改架构。

这是我的表格的大纲:

如有任何帮助，我们将不胜感激!

Answer 1

采用 Bill Karwins 的方法来解决此类问题，以找到任何 invoice_id 的最近交易:

SELECT
    it.invoice_id,
    t1.*
FROM
    invoice_transaction it
INNER JOIN
    transaction t1
ON
    it.transaction_id = t1.id
LEFT OUTER JOIN
    transaction t2
ON
    (it.transaction_id = t2.id
AND
    (t1.created_at < t2.created_at OR t1.created_at = t2.created_at AND t1.id < t2.id))
WHERE
    t2.id IS NULL

为您提供 t1 作为该 invoice_id 的最新交易。因此，您只需加入发票表即可获取此发票的详细信息:

SELECT
    i.*
    it.invoice_id,
    t1.*
FROM
    invoice_transaction it
-- we can add our join to the invoice table here
INNER JOIN
    invoice i
ON
    it.invoice_id = i.id
-- and we're done
INNER JOIN
    transaction t1
ON
    it.transaction_id = t1.id
LEFT OUTER JOIN
    transaction t2
ON
    (it.transaction_id = t2.id
AND
    (t1.created_at < t2.created_at OR t1.created_at = t2.created_at AND t1.id < t2.id))
WHERE
    t2.id IS NULL

现在，您可以通过将此添加到 WHERE 子句来过滤具有特定金额或在特定日期到期的发票。

这就是我的意思，调整您已经在 StackOverflow 上找到的解决方案应该不会太困难。