MySql 查询 Order BY datetime 和 Group By Id 问题

Question

此查询有问题

评论表中有 3 行具有不同的日期时间，我想要包含最后评论创建日期/更新日期的客户列表。 但没有按客户分组获得最后评论的客户

SELECT * FROM(
SELECT MAX(comments.`date_updated`), customer.id AS vid, comments.`date_updated` AS dts, comments.`id` AS comments_id, comments.* FROM customer
INNER JOIN comments ON comments.`customer_id` = customer.`id`
 WHERE customer.`id` IN ('')
) AS v
GROUP BY v.`vid` LIMIT 0,50

Answer 1

您使用自联接来评论表并筛选具有最新date_updated的每个客户的行

SELECT c.id AS vid, co.`date_updated` AS dts, co.`id` AS comments_id, co.*
FROM customer c
INNER JOIN comments co ON co.`customer_id` = c.`id`
LEFT JOIN comments co1 ON co.`customer_id` = co1.`customer_id` AND co.date_updated < co1.date_updated
WHERE co1.customer_id IS NULL  AND c.`id` IN ('')

或者使用内连接

SELECT c.id AS vid, co.`date_updated` AS dts, co.`id` AS comments_id, co.*
FROM customer c
INNER JOIN comments co ON co.`customer_id` = c.`id`
INNER JOIN (
    SELECT customer_id, MAX(date_updated) date_updated
    FROM comments
    GROUP BY customer_id
) co1 ON co.customer_id = co1.customer_id AND co.date_updated =  co1.date_updated
WHERE c.`id` IN ('')