php - Sql 整理出 "viral"主题(阅读次数最多的新主题)

Question

我有下面的代码，可以正常工作。但我想向此函数添加一个 sql 查询，以排序“最常阅读/查看的新主题”。

正如您所看到的，已经有一个用于排序查看次数最多的主题的查询 - “sql_views”，我想复制并更改该查询，以便它只在 100 个最新主题(id)中搜索。这可能是一个新的查询“sql_newview”或类似的查询。

我一直坚持这个问题，但我想这对于查询大师来说非常简单，所以我希望在这里得到帮助!

例如:在 TOPICS_TABLE 中查找最后 100 个条目，并使用 t.topic_views DESC 对这些条目进行排序。

$sql_main = "SELECT  t.*, MAX(p.post_id) AS last_post
                FROM " . POSTS_TABLE . " AS p, " . TOPICS_TABLE . " AS t
                WHERE {available_forums_in_set}
                    AND p.topic_id = t.topic_id AND p.forum_id = t.forum_id
                GROUP BY p.topic_id";
$sql_tt_avails = array(
    'sql_topics'    => array(
        'sql'   =>  "{$sql_main}
                    ORDER BY t.topic_time DESC",
        'field' =>  't.forum_id',
        ),
    'sql_replies'   => array(
        'sql'   =>  "{$sql_main}
                    ORDER BY t.topic_replies DESC, t.topic_time DESC",
        'field' =>  't.forum_id',
        ),
    'sql_views'     => array(
        'sql'   =>  "{$sql_main}
                    ORDER BY t.topic_views DESC, t.topic_time DESC",
        'field' =>  't.forum_id',
        ),
    'sql_posts'     => array(
        'sql'   =>  "{$sql_main}
                    ORDER BY t.topic_last_post_time DESC",
        'field' =>  't.forum_id',
        ),
);

编辑:这是完整的(工作)查询，首先是“最常查看/阅读的主题”

SELECT t.*, MAX(p.post_id) AS last_post FROM phpbb_posts AS p, phpbb_topics AS t WHERE t.forum_id IN (1, 2, 3, 4, 5, 6, 8, 9, 10, 11, 12, 13, 14, 15, 17, 18, 19, 20, 21, 22, 24, 25, 26, 27, 28, 29, 30, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 106, 107, 108, 109, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125) AND p.topic_id = t.topic_id AND p.forum_id = t.forum_id GROUP BY p.topic_id ORDER BY t.topic_views DESC, t.topic_time DESC LIMIT 5

这里是“最新主题”的查询

SELECT t.*, MAX(p.post_id) AS last_post FROM phpbb_posts AS p, phpbb_topics AS t WHERE t.forum_id IN (1, 2, 3, 4, 5, 6, 8, 9, 10, 11, 12, 13, 14, 15, 17, 18, 19, 20, 21, 22, 24, 25, 26, 27, 28, 29, 30, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 106, 107, 108, 109, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125) AND p.topic_id = t.topic_id AND p.forum_id = t.forum_id GROUP BY p.topic_id ORDER BY t.topic_time DESC LIMIT 5

我尝试了多次编辑，但无法让“sql_views”从 100 个最新主题中对“查看次数最多的主题”进行排序。

Answer 1

SQL 有一个限制子句，例如 SELECT * FROM {table} WHERE {condition} LIMIT 100