mysql - 如何从多个表的左连接中选择最大日期

Question

我有一个发票表、credit_memo 表和付款表。我无法从发票到期日、付款签发日期或贷项凭证签发日期返回最长日期。

SELECT
  T1.b2b_id,
  MAX(T1.due_date) | coalesce(T3.issue_date, '1000-01-01') | 
    coalesce(T4.issue_date, '10001-01-01') AS endDate
FROM t_invoice AS T1
   LEFT JOIN t_linked_transaction AS T2 ON T2.invoice_uid = T1.uid
   LEFT JOIN t_payment AS T3 ON T3.uid = T2.payment_uid
   LEFT JOIN t_credit_memo AS T4 ON T4.uid = t2.credit_memo_uid

MAX 查询返回 20150301009885。

正确的日期是 2015-03-01 00:00:00，如果我用 DATE(MAX(...)) 包装 MAX 查询，它将返回 NULL

感谢您的帮助

Answer 1

您需要根据此处链接中指定的 MySQL 错误转换为 Char

MySQL Bug MAX(datetime)

SELECT
  T1.b2b_id,
  MAX (CAST(T1.due_date) AS CHAR) | coalesce(T3.issue_date, '1000-01-01') | 
    coalesce(T4.issue_date, '10001-01-01') AS endDate
FROM t_invoice AS T1
   LEFT JOIN t_linked_transaction AS T2 ON T2.invoice_uid = T1.uid
   LEFT JOIN t_payment AS T3 ON T3.uid = T2.payment_uid
   LEFT JOIN t_credit_memo AS T4 ON T4.uid = t2.credit_memo_uid