如何修改SQL视图以统计点赞/评论数及判断用户互动状态

首先先帮你修正原视图里的几个小问题：

• 表名拼写错误：topic应该是topics（你的表定义里明确是topics）
• 字段拼写错误：profiepic应该是profilepic
• 推荐用现代的INNER JOIN语法替代旧的逗号分隔表写法，可读性和可维护性更强

接下来我们扩展视图，实现你需要的点赞数、评论数统计，以及当前用户的互动状态判断。下面以MySQL为例（如果是PostgreSQL等其他数据库，语法会稍有调整，我会在后面补充说明）：

CREATE VIEW TOPICS_VIEW AS
SELECT
    t.topic_id,
    t.topic_by_id AS TBYID,
    t.topic_title,
    t.topic_data,  -- 补上原表的主题内容和时间戳，大概率你会需要这些字段
    t.timestamp AS topic_timestamp,
    u.user_id AS UserID,
    u.name,
    u.profilepic,
    -- 统计点赞数：用LEFT JOIN保证无点赞的主题也能保留，COUNT会自动忽略NULL值
    COUNT(l.liked_by_id) AS like_count,
    -- 统计评论数
    COUNT(c.comment_by_id) AS comment_count,
    -- 判断当前用户是否点过赞：用EXISTS检查当前用户的点赞记录
    CASE
        WHEN EXISTS(
            SELECT 1 FROM likes l_check
            WHERE l_check.topic_id = t.topic_id
              AND l_check.liked_by_id = @current_user_id  -- 替换成你的当前用户变量/参数
        ) THEN 1
        ELSE 0
    END AS has_liked,
    -- 判断当前用户是否留过评论
    CASE
        WHEN EXISTS(
            SELECT 1 FROM comments c_check
            WHERE c_check.topic_id = t.topic_id
              AND c_check.comment_by_id = @current_user_id
        ) THEN 1
        ELSE 0
    END AS has_commented
FROM topics t
INNER JOIN users u ON u.user_id = t.topic_by_id
LEFT JOIN likes l ON l.topic_id = t.topic_id
LEFT JOIN comments c ON c.topic_id = t.topic_id
GROUP BY t.topic_id, t.topic_by_id, t.topic_title, t.topic_data, t.timestamp, u.user_id, u.name, u.profilepic;

关键细节说明： #

• LEFT JOIN的必要性：如果用INNER JOIN，没有点赞或评论的主题会被直接过滤掉，LEFT JOIN能保证所有主题都出现在结果里，无互动的主题计数会显示为0。
• GROUP BY的规范：因为用到了COUNT聚合函数，必须把所有非聚合字段都放到GROUP BY中（不同数据库的宽松模式可能允许省略，但严格模式下必须写全，推荐严格遵守规范避免潜在问题）。
• 当前用户变量：@current_user_id是MySQL的用户变量，使用视图前需要先赋值，比如SET @current_user_id = 123;。如果是PostgreSQL，可以创建带参数的视图，写法如下：

CREATE VIEW TOPICS_VIEW(current_user_id) AS
SELECT
    t.topic_id,
    t.topic_by_id AS TBYID,
    t.topic_title,
    t.topic_data,
    t.timestamp AS topic_timestamp,
    u.user_id AS UserID,
    u.name,
    u.profilepic,
    COUNT(l.liked_by_id) AS like_count,
    COUNT(c.comment_by_id) AS comment_count,
    EXISTS(
        SELECT 1 FROM likes l_check
        WHERE l_check.topic_id = t.topic_id
          AND l_check.liked_by_id = current_user_id
    ) AS has_liked,
    EXISTS(
        SELECT 1 FROM comments c_check
        WHERE c_check.topic_id = t.topic_id
          AND c_check.comment_by_id = current_user_id
    ) AS has_commented
FROM topics t
INNER JOIN users u ON u.user_id = t.topic_by_id
LEFT JOIN likes l ON l.topic_id = t.topic_id
LEFT JOIN comments c ON c.topic_id = t.topic_id
GROUP BY t.topic_id, t.topic_by_id, t.topic_title, t.topic_data, t.timestamp, u.user_id, u.name, u.profilepic;

使用时直接传入用户ID：SELECT * FROM TOPICS_VIEW(123);

• 去重计数：如果业务允许同一用户多次点赞同一主题，想要统计独立点赞用户数，可以把COUNT(l.liked_by_id)改成COUNT(DISTINCT l.liked_by_id)，评论数同理。

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