在这里，聚合不是解决方案。您希望从联接表中获得整行，因此建议改为筛选。如果您运行的是mysql 8.0，我建议您使用以下窗口函数：

SELECT *
FROM (
    SELECT
        i.customer_sequence,
        i.due_date,
        ROW_NUMBER() OVER(PARTITION BY i.customer_sequence ORDER BY cn.datetime DESC) rn,
        cn.datetime as notes_datetime,
        cn.user as notes_user,
        cn.notes as notes_notes
    FROM billing_invoices i
    LEFT JOIN customer_notes cn
        ON  i.customer_sequence = cn.customer_seq
        AND cn.type = 'Accounts'
    WHERE
        i.customer_sequence <> '0' AND
        i.status = 'Unpaid' AND
        i.directdebit <> 'Y'
) t
ORDER BY i.due_date DESC

注意，我在 left join 把table从 WHERE 条款 ON join的子句（否则，它的行为类似于 inner join ).
在早期版本中，一个选项是相关子查询：

SELECT
    i.customer_sequence,
    i.due_date,
    cn.datetime as notes_datetime,
    cn.user as notes_user,
    cn.notes as notes_notes
FROM billing_invoices i
LEFT JOIN customer_notes cn
    ON  i.customer_sequence = cn.customer_seq
    AND cn.type = 'Accounts'
    AND cn.datetime = (
        SELECT MAX(cn1.datetime)
        FROM customer_notes cn1
        WHERE i.customer_sequence = cn1.customer_seq AND cn1.type = 'Accounts'
    )
WHERE
    i.customer_sequence <> '0' AND
    i.status = 'Unpaid' AND
    i.directdebit <> 'Y'