MySQL 在选择之前按日期排序

Question

我正在尝试获取所有主题以及每个主题中的最后一条评论。我已经尝试了几个不同的 sql 语句，但没有成功。

SELECT 
    a.*, 
    b.*, 
    c.*, 
   (SELECT COUNT(*) FROM comments WHERE comment_topic_id = a.topic_id) AS count
FROM topics AS a 
LEFT JOIN categories AS b ON a.topic_category = b.category_id
LEFT JOIN (
    SELECT *
    FROM comments
    ORDER BY comment_date DESC
) AS c ON a.topic_id = c.comment_topic_id
WHERE b.category_id = '1' AND b.category_permission <= '2'
ORDER BY a.topic_created ASC

上面的代码将为每个评论而不是最近的评论生成一个结果。感谢任何帮助，我可以提供图像来说明数据库和表结构

Answer 1

我已经更改了您的 count 的别名，因为 count 是一个保留字。

试试这个:

编辑

SELECT 
    a.*, 
    b.*, 
    co.*, 
   (SELECT COUNT(*) FROM comments WHERE comment_topic_id = a.topic_id) AS tot_comment
FROM topics AS a 
JOIN categories AS b ON a.topic_category = b.category_id
LEFT JOIN (
    SELECT *
    FROM comments c
    WHERE NOT EXISTS(
        SELECT 'NEXT'
        FROM comments c2
        WHERE c2.comment_topic_id = c.comment_topic_id
        AND c2.comment_date > c.comment_date
    )
) AS co ON a.topic_id = co.comment_topic_id
WHERE b.category_id = '1' AND b.category_permission <= '2'
ORDER BY a.topic_created ASC