如何构建MySQL查询获取带作者邮箱、点赞数及点赞状态的帖子？

下面是满足需求的SQL查询语句，你需要将:target_user_id替换为实际要查询的用户ID（比如1）：

SELECT
    p.postId,
    p.userId,
    p.post,
    u.email,
    COUNT(l.postId) AS likes_count,
    IF(EXISTS(SELECT 1 FROM likes l2 WHERE l2.postId = p.postId AND l2.userId = :target_user_id), TRUE, FALSE) AS isLiked
FROM posts p
LEFT JOIN users u ON p.userId = u.userId
LEFT JOIN likes l ON p.postId = l.postId
GROUP BY p.postId, p.userId, p.post, u.email;

核心逻辑说明： #

• 关联用户表：通过LEFT JOIN关联users表，获取帖子作者的邮箱，保证帖子信息不会因作者数据缺失而丢失
• 统计点赞数：利用LEFT JOIN关联likes表，通过COUNT(l.postId)统计每个帖子的总点赞数，未被点赞的帖子会返回0
• 判断是否点赞：使用EXISTS子查询检查指定用户是否对当前帖子存在点赞记录，结合IF函数返回true或false
• 分组规则：GROUP BY包含所有非聚合字段，符合MySQL的分组要求，避免出现分组错误

如果更倾向于用JOIN而非子查询实现isLiked，可以使用以下写法：

SELECT
    p.postId,
    p.userId,
    p.post,
    u.email,
    COUNT(l.postId) AS likes_count,
    CASE WHEN l2.userId IS NOT NULL THEN TRUE ELSE FALSE END AS isLiked
FROM posts p
LEFT JOIN users u ON p.userId = u.userId
LEFT JOIN likes l ON p.postId = l.postId
LEFT JOIN likes l2 ON p.postId = l2.postId AND l2.userId = :target_user_id
GROUP BY p.postId, p.userId, p.post, u.email, l2.userId;

这种方式通过二次关联likes表并指定用户ID，通过判断关联结果是否为空来确定是否点赞。

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