时间戳

首页 > 解决方案 > LEFT JOIN 最新记录

mysql - LEFT JOIN 最新记录

问题描述

我查看了几个似乎是解决方案的答案，但我很难理解它。

jos_payplans_invoice我下面的查询包含对and的左连接jos_payplans_transaction。我需要获取最新发票 ( b.invoice_id) 以及该发票的最新交易 ( c.transaction_id)。

据我了解，我需要子查询，但不知道如何。

SELECT 
    a.subscription_id,
    b.invoice_id,
    c.transaction_id 
FROM jos_payplans_subscription AS a 
LEFT JOIN jos_payplans_invoice AS b ON b.object_id = a.order_id 
LEFT JOIN jos_payplans_transaction AS c ON c.invoice_id = b.invoice_id 
GROUP BY a.subscription_id
ORDER BY c.transaction_id DESC

注意：jos_payplans_invoice.object_id = jos_payplans_subscription.order_id

jos_payplans_subscription：

jos_payplans_subscription

jos_payplans_invoice：

jos_payplans_invoice

jos_payplans_transaction：

在此处输入图像描述

标签： mysql

解决方案

这里可能有一些重构的机会（特别是如果您使用的是支持窗口函数的更新版本的 mysql）。不过，这应该会让你进入球场：

transaction_id获取每个的最后一个invoice_id

SELECT transaction_id, invoice_id 
FROM jos_payplans_transactions jpt
WHERE create_date = 
    (
        SELECT max(create_date)
        FROM jos_payplans_transactions
        WHERE jpt.invoice_id = invoice_id
    )

object_id获取每个的最后一个order_id：

SELECT invoice_id, object_id
FROM jos_payplans_invoice jpi
WHERE create_date = 
    (
        SELECT max(create_date)
        FROM jos_payplans_invoice
        WHERE jpi.object_id = object_id
    )

把它们放在一起：

SELECT
    a.subscription_id,
    b.invoice_id,
    c.transaction_id
FROM jos_payplans_subscription as a
    LEFT OUTER JOIN 
        (
            SELECT invoice_id, object_id
            FROM jos_payplans_invoice jpi
            WHERE create_date = 
                (
                    SELECT max(create_date)
                    FROM jos_payplans_invoice
                    WHERE jpi.object_id = object_id
                )
        ) b on a.order_id = b.object_id
    LEFT OUTER JOIN 
        (
            SELECT transaction_id, invoice_id 
            FROM jos_payplans_transactions jpt
            WHERE create_date = 
                (
                    SELECT max(create_date)
                    FROM jos_payplans_transactions
                    WHERE jpt.invoice_id = invoice_id
                )
        ) c on b.invoice_id = c.invoice_id;

较新版本的 Mysql (8.0+) 可以处理窗口函数，这将有助于提高性能，因为只需要对每个表进行一次扫描：

SELECT
    a.subscription_id,
    b.invoice_id,
    c.transaction_id
FROM jos_payplans_subscription as a
    LEFT OUTER JOIN 
        (
            SELECT invoice_id, 
                object_id, 
                ROW_NUMBER() OVER (PARTITION BY object_id ORDER BY create_date) as rn
            FROM jos_payplans_invoice jpi               
        ) b on a.order_id = b.object_id
            AND b.rn = 1
    LEFT OUTER JOIN 
        (
            SELECT transaction_id, 
                invoice_id ,
                ROW_NUMBER() OVER (PARTITION BY invoice_id ORDER BY create_date) as rn
            FROM jos_payplans_transactions jpt              
        ) c on b.invoice_id = c.invoice_id
            AND c.rn = 1;

推荐阅读