php - 左加入所有主题仅按粘性最后帖子顺序和最后帖子 DESC

Question

我正在尝试根据论坛 ID 获取所有主题。这些主题需要首先按粘性排序，然后按最后发布日期排序。

我有这个查询，工作得几乎正常，但它没有按照我想要的方式对主题进行排序。

SELECT 
    forum_posts.posted_by, 
    forum_posts.posted, 
    forum_topics.id, 
    forum_topics.subject, 
    forum_topics.sticky, 
    forum_topics.closed 
FROM 
    forum_posts 
LEFT JOIN 
    forum_topics 
ON 
    forum_topics.id=forum_posts.topic_id 
WHERE forum_topics.forum_id=$forumdata->id 
GROUP BY forum_topics.id 
ORDER BY forum_posts.posted DESC

Answer 1

如果我正确地阅读了您的问题，那么您只需对 ORDER BY 子句进行轻微更改即可:

ORDER BY
    forum_topics.sticky,          -- just add this
    forum_posts.posted DESC;

但是，当您选择非聚合列时，我的预感是您确实应该使用子查询来找出最新的帖子:

SELECT
    ft.*, fp1.*
FROM forum_posts fp1
INNER JOIN
(
    SELECT topic_id, MAX(posted) AS max_posted
    FROM forum_posts
    GROUP BY topic_id
) fp2
    ON fp1.topic_id = fp2.topic_id AND
       fp1.posted = fp2.max_posted
LEFT JOIN forum_topics ft
    ON fp1.id = ft.topic_id
ORDER BY
    ft.sticky;