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

Question

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

我目前在 WHERE 中使用这个 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

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

根据下面 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