MySQL查询问题：获取最高浏览量帖子及各话题最热帖子

嘿，我来帮你搞定这两个MySQL查询需求，分两种场景一步步拆解清楚：

首先我们得先统计每篇帖子的总浏览量，再找出其中的Top帖子，这里有两种常用解法：

方法1：聚合函数 + 子查询（兼容所有MySQL版本） #

先通过views表按post_id分组统计总浏览数，再筛选出等于最大浏览数的帖子：

SELECT p.id, p.title, v.total_views
FROM post p
JOIN (
    SELECT post_id, COUNT(*) AS total_views
    FROM views
    GROUP BY post_id
) v ON p.id = v.post_id
WHERE v.total_views = (
    SELECT MAX(total_views)
    FROM (
        SELECT COUNT(*) AS total_views
        FROM views
        GROUP BY post_id
    ) temp
);

如果有多篇帖子并列最高浏览量，这个查询会返回所有符合条件的帖子。

方法2：窗口函数（MySQL 8.0+ 更简洁） #

用窗口函数直接给每个帖子的浏览量排名，取排名第一的结果：

SELECT id, title, total_views
FROM (
    SELECT 
        p.id, 
        p.title, 
        COUNT(v.id) AS total_views,
        RANK() OVER (ORDER BY COUNT(v.id) DESC) AS view_rank
    FROM post p
    LEFT JOIN views v ON p.id = v.post_id
    GROUP BY p.id, p.title
) ranked_posts
WHERE view_rank = 1;

• 用RANK()会保留并列排名（比如两个帖子都是第一，都会返回）；如果只想返回一个，换成ROW_NUMBER()即可
• 如果只考虑有浏览记录的帖子，把LEFT JOIN换成INNER JOIN就行

因为topic和post是多对多关系，首先得有一个中间关联表（比如post_topic，包含post_id和topic_id两个字段）。下面分两种版本给出解法：

方法1：窗口函数分组排名（MySQL 8.0+ 推荐） #

这是处理分组内取Top场景最清晰的方式：

SELECT topic_id, topic_name, post_id, post_title, total_views
FROM (
    SELECT 
        t.id AS topic_id,
        t.name AS topic_name,
        p.id AS post_id,
        p.title AS post_title,
        COUNT(v.id) AS total_views,
        RANK() OVER (PARTITION BY t.id ORDER BY COUNT(v.id) DESC) AS topic_view_rank
    FROM topic t
    JOIN post_topic pt ON t.id = pt.topic_id
    JOIN post p ON pt.post_id = p.id
    LEFT JOIN views v ON p.id = v.post_id
    GROUP BY t.id, t.name, p.id, p.title
) ranked_topic_posts
WHERE topic_view_rank = 1;

细节说明： #

• PARTITION BY t.id表示按Topic分组，每个Topic内单独计算排名
• RANK()会保留并列情况：如果一个Topic下有多篇帖子浏览量相同且都是最高，都会被返回
• 若要严格只返回每个Topic的一个帖子（哪怕有并列），把RANK()换成ROW_NUMBER()即可
• LEFT JOIN views确保没有浏览记录的帖子也会被统计（总浏览量为0），如果不需要这类帖子，换成INNER JOIN

方法2：兼容低版本MySQL（无窗口函数） #

如果你的MySQL版本低于8.0，用关联子查询实现：

SELECT 
    t.id AS topic_id,
    t.name AS topic_name,
    p.id AS post_id,
    p.title AS post_title,
    COUNT(v.id) AS total_views
FROM topic t
JOIN post_topic pt ON t.id = pt.topic_id
JOIN post p ON pt.post_id = p.id
LEFT JOIN views v ON p.id = v.post_id
GROUP BY t.id, t.name, p.id, p.title
HAVING COUNT(v.id) = (
    SELECT MAX(post_view_count)
    FROM (
        SELECT COUNT(v2.id) AS post_view_count
        FROM post_topic pt2
        JOIN post p2 ON pt2.post_id = p2.id
        LEFT JOIN views v2 ON p2.id = v2.post_id
        WHERE pt2.topic_id = t.id
        GROUP BY p2.id
    ) temp
);

这个逻辑是：对每个Topic，先通过子查询找出该Topic下帖子的最大浏览量，再筛选出等于这个最大值的帖子。

内容的提问来源于stack exchange，提问作者lulliezy