获取每个不同客户ID中的最后一列

Question

我有一个包含发票日期和客户ID的表(当然还有其他数据)。我希望在每个客户的表中显示未来最长的发票日期。

我当前在以下位置使用此MySQL：

WHERE o70vm_invoices_invoices.invoice_date = (SELECT MAX(o70vm_invoices_invoices.invoice_date) FROM o70vm_invoices_invoices)";

此代码当前在列中仅显示一个客户的最旧日期。

我在下面包含了我的整个MySQL查询，请记住，我必须从不同的表中提取数据来显示我需要的数据。

$query = "SELECT       
    o70vm_invoices_contacts.name AS 'Parent_Name',
    o70vm_invoices_contacts.id AS 'Parent_ID',
    o70vm_invoices_contacts.company AS 'Children',
    o70vm_invoices_contacts.active AS 'Active',

    o70vm_invoices_invoices.id AS 'Invoice_ID',
    o70vm_invoices_invoices.user_id AS 'Parent_ID_on_Invoice',
    o70vm_invoices_invoices.invoice_num AS 'Invoice_Num',
    o70vm_invoices_invoices.invoice_date AS 'Invoice_Date',
    o70vm_invoices_invoices.invoice_duedate AS 'Invoice_Date_Due',

    o70vm_invoices_payments.invoice_id,
    o70vm_invoices_payments.id,
    o70vm_invoices_payments.payment_amount AS 'Payment_Amount',
    o70vm_invoices_payments.payment_datetime AS 'Payment_Date',
    o70vm_invoices_payments.payment_duedate,
    o70vm_invoices_payments.payment_status AS 'Payment_Status',
    o70vm_invoices_payments.payment_description AS 'Payment_Descript',
    o70vm_invoices_payments.payment_type AS 'Payment_Type'

    FROM o70vm_invoices_invoices, o70vm_invoices_contacts, o70vm_invoices_payments
    WHERE
    o70vm_invoices_contacts.id = o70vm_invoices_invoices.user_id
    AND
    o70vm_invoices_payments.invoice_id = o70vm_invoices_invoices.id
    AND
    o70vm_invoices_invoices.invoice_date < '2099-01-01'
    AND
    o70vm_invoices_contacts.active = 1
    AND o70vm_invoices_invoices.invoice_date = (SELECT MAX(o70vm_invoices_invoices.invoice_date) FROM o70vm_invoices_invoices)";

如果我使用GROUP BY：

GROUP BY o70vm_invoices_contacts.id

我可以拉出每个客户来显示，但它不会在表中显示最后一张将来的发票。

根据Stefan的评论，下面是我修改后的子查询：

$query = "SELECT   *
FROM   ( SELECT 
    o70vm_invoices_contacts.name AS 'Parent_Name',
    o70vm_invoices_contacts.id AS 'Parent_ID',
    o70vm_invoices_contacts.company AS 'Children',
    o70vm_invoices_contacts.active AS 'Active',

    o70vm_invoices_invoices.id AS 'Invoice_ID2',
    o70vm_invoices_invoices.user_id AS 'Parent_ID_on_Invoice',
    o70vm_invoices_invoices.invoice_num AS 'Invoice_Num',
    o70vm_invoices_invoices.invoice_date AS 'Invoice_Date',
    o70vm_invoices_invoices.invoice_duedate AS 'Invoice_Date_Due',

    o70vm_invoices_payments.invoice_id,
    o70vm_invoices_payments.id,
    o70vm_invoices_payments.payment_amount AS 'Payment_Amount',
    o70vm_invoices_payments.payment_datetime AS 'Payment_Date',
    o70vm_invoices_payments.payment_duedate,
    o70vm_invoices_payments.payment_status AS 'Payment_Status',
    o70vm_invoices_payments.payment_description AS 'Payment_Descript',
    o70vm_invoices_payments.payment_type AS 'Payment_Type'

    FROM o70vm_invoices_invoices, o70vm_invoices_contacts, o70vm_invoices_payments
    WHERE
    o70vm_invoices_contacts.id = o70vm_invoices_invoices.user_id
    AND
    o70vm_invoices_payments.invoice_id = o70vm_invoices_invoices.id
    AND
    o70vm_invoices_invoices.invoice_date < '2099-01-01'
    AND
    o70vm_invoices_contacts.active = 1
    ORDER BY o70vm_invoices_invoices.invoice_date DESC

    ) AS tmp
    GROUP BY tmp.id";

此查询列出第一个发票日期，而不是最后一个发票日期。如果我将订单更改为ASC，它只会对第一个发票日期的结果进行排序。

如果能帮上忙，非常感谢。

Answer 1

最好的方法可能是使用子查询：

SELECT * 
FROM (
    SELECT * 
    FROM o70vm_invoices_invoices 
    ORDER BY invoice_date DESC
) AS tmp
GROUP BY tmp.id

Answer 2

我相信有一种更好的方法可以做到这一点；然而，这是可行的。

我刚刚在我的查询中添加了MAX：

max(o70vm_invoices_invoices.invoice_date) AS 'Invoice_Date',

这将列出每个客户的最远日期。