MySQL JOIN 最后一条记录

Question

我正在尝试将“corr”表的最新记录加入到我的查询的其余部分。它不是最后一条记录，而是加入第一条记录。

有什么建议吗？

SELECT c.*,
       cc.*,
       com.*,
       cor.corr_id,
       cor.corr_method,
       cor.corr_action,
       cor.corr_description,
       cor.corr_date_added
FROM contacts c
LEFT JOIN companies_contacts cc
    ON c.contact_id = cc.contact_id
LEFT JOIN companies com
    ON com.company_id = cc.company_id
LEFT JOIN
    (SELECT contact_id,
            corr_id,
            MAX(corr_date_added) AS max_date_added
     FROM corr
     GROUP BY contact_id) y
    ON y.contact_id = c.contact_id
LEFT JOIN corr cor
    ON cor.corr_id = y.corr_id
GROUP BY cc.com_con_id
ORDER BY c.contact_last_name ASC
LIMIT 0, 25

这行得通:

SELECT c.*, cc.*, com.*, cor.corr_id, cor.corr_method, cor.corr_action, cor.corr_description, cor.corr_date_added 
FROM contacts c 
LEFT JOIN companies_contacts cc ON c.contact_id = cc.contact_id 
LEFT JOIN companies com ON com.company_id = cc.company_id 
LEFT JOIN (SELECT max.contact_id, max.corr_id, MAX(max.corr_date_added) AS max_date_added 
FROM corr max GROUP BY max.contact_id ) y ON y.contact_id = cc.contact_id 
LEFT JOIN corr cor ON max_date_added = cor.corr_date_added AND y.contact_id = cor.contact_id
GROUP BY cc.com_con_id 
ORDER BY c.contact_last_name ASC LIMIT 0,25

Answer 1

这有效:

SELECT c.*, cc.*, com.*, cor.corr_id, cor.corr_method, cor.corr_action, cor.corr_description, cor.corr_date_added 
FROM contacts c 
LEFT JOIN companies_contacts cc ON c.contact_id = cc.contact_id 
LEFT JOIN companies com ON com.company_id = cc.company_id 
LEFT JOIN (SELECT max.contact_id, max.corr_id, MAX(max.corr_date_added) AS max_date_added 
FROM corr max GROUP BY max.contact_id ) y ON y.contact_id = cc.contact_id 
LEFT JOIN corr cor ON max_date_added = cor.corr_date_added AND y.contact_id = cor.contact_id
GROUP BY cc.com_con_id 
ORDER BY c.contact_last_name ASC LIMIT 0,25