php - 如何按其他表中的最新记录对记录进行排序

Question

我正在尝试按最近的事件(评论)对报表进行排序。但是，如果语句没有注释，则下面的查询不会在末尾显示该语句。如何更改查询，使没有注释的语句显示并最后显示？

SELECT 
  `statements`.`id`,
  `statement`,
  `statements`.`timestamp`,
  `statements`.`published`,
  `image`,
  `statements`.`author_id`,
  `statements`.`ip_address`,
  `username`,
  `comment_count` 
FROM
  (`statements`) 
  INNER JOIN `comments` 
    ON `comments`.`statement_id` = `statements`.`id` 
  LEFT JOIN `users` 
    ON `users`.`id` = `statements`.`author_id` 
WHERE `statements`.`published` > 0 
GROUP BY `statements`.`id` 
ORDER BY MAX(comments.id) 

Answer 1

为 comments 表尝试 LEFT JOIN:

SELECT `statements`.`id`, `statement`, `statements`.`timestamp`, `statements`.`published`, `image`, `statements`.`author_id`, `statements`.`ip_address`, `username`, `comment_count`
FROM (`statements`) 
    LEFT JOIN `comments` ON `comments`.`statement_id` = `statements`.`id` 
    LEFT JOIN `users` ON `users`.`id` = `statements`.`author_id` 
WHERE `statements`.`published` > 0
GROUP BY `statements`.`id` 
ORDER BY MAX(comments.id) DESC

按最大评论 ID 降序排列会将带有最新评论的语句放在顶部。

关于 MySQL 文档 Working with NULL Values

When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC.

没有注释的语句 (MAX(comments.id) IS NULL) 必须放在结果的底部。