通过JOIN优化嵌套SQL查询：点赞踩数及用户状态统计问题

需求 #

从数据库中获取帖子列表，同时获取帖子的likes、dislikes数量，以及当前用户是否点赞/踩该帖子。

已尝试方案 #

方案1：嵌套子查询（结果准确） #

最初采用嵌套子查询实现需求，统计结果正确：

SELECT
announcements.*, 
users.FIRSTNAME, 
users.LASTNAME,
((SELECT COUNT(USER_ID) FROM likes_posts WHERE POST_ID = announcements.ID) - (SELECT COUNT(USER_ID) FROM dislikes_posts WHERE POST_ID = announcements.ID)) as TLIKES,
(SELECT COUNT(USER_ID) FROM likes_posts WHERE USER_ID = ? AND POST_ID = announcements.ID) AS USER_LIKED,
(SELECT COUNT(USER_ID) FROM dislikes_posts WHERE USER_ID = ? AND POST_ID = announcements.ID) AS USER_DISLIKED 
FROM announcements 
LEFT JOIN users ON announcements.OWNER_ID = users.ID
WHERE announcements.CHANNEL = ? AND announcements.ID < ? 
ORDER BY announcements.ID DESC

方案2：多JOIN尝试（结果异常） #

尝试用JOIN优化查询，但统计数值严重偏大：

SELECT
announcements.*, 
users.FIRSTNAME, 
users.LASTNAME,
COUNT(likes_posts.USER_ID) AS TLikes,
COUNT(dislikes_posts.USER_ID) AS TDislikes,
UserLiked.ID AS userLiked,
UserDisliked.ID AS userDisliked
FROM announcements
LEFT JOIN likes_posts ON likes_posts.POST_ID = announcements.ID
LEFT JOIN dislikes_posts ON dislikes_posts.POST_ID = announcements.ID
LEFT JOIN likes_posts AS UserLiked ON UserLiked.USER_ID = ?
LEFT JOIN likes_posts AS UserDisliked ON UserDisliked.USER_ID = ?
LEFT JOIN users ON announcements.OWNER_ID = users.ID
WHERE announcements.CHANNEL = ? AND announcements.ID < ? 
GROUP BY announcements.ID
ORDER BY announcements.ID DESC

查询结果对比 #

• 方案1能准确统计点赞/踩数，比如实际5赞3踩会正确返回对应数值。
• 方案2的统计值远大于实际数量，比如实际5赞6踩时，结果会显示16赞16踩。

问题原因 #

方案2的核心问题是笛卡尔积导致重复计数：同时关联likes_posts和dislikes_posts时，若一个帖子有A个赞、B个踩，JOIN后会生成A×B条重复记录，COUNT时会把这些重复记录全部计入统计，导致数值偏大。此外，UserLiked和UserDisliked的关联条件缺失了POST_ID匹配，逻辑本身存在错误。

优化后的JOIN方案 #

先对点赞、踩数分别做聚合统计，再关联主表避免笛卡尔积，同时准确判断用户互动状态：

SELECT
    a.*,
    u.FIRSTNAME,
    u.LASTNAME,
    COALESCE(l.like_count, 0) - COALESCE(d.dislike_count, 0) AS TLIKES,
    COALESCE(ul.user_liked, 0) AS USER_LIKED,
    COALESCE(ud.user_disliked, 0) AS USER_DISLIKED
FROM announcements a
LEFT JOIN users u ON a.OWNER_ID = u.ID
-- 预聚合每个帖子的点赞总数
LEFT JOIN (
    SELECT POST_ID, COUNT(USER_ID) AS like_count
    FROM likes_posts
    GROUP BY POST_ID
) l ON l.POST_ID = a.ID
-- 预聚合每个帖子的踩总数
LEFT JOIN (
    SELECT POST_ID, COUNT(USER_ID) AS dislike_count
    FROM dislikes_posts
    GROUP BY POST_ID
) d ON d.POST_ID = a.ID
-- 判断当前用户是否点赞该帖子
LEFT JOIN (
    SELECT POST_ID, 1 AS user_liked
    FROM likes_posts
    WHERE USER_ID = ?
) ul ON ul.POST_ID = a.ID
-- 判断当前用户是否踩该帖子
LEFT JOIN (
    SELECT POST_ID, 1 AS user_disliked
    FROM dislikes_posts
    WHERE USER_ID = ?
) ud ON ud.POST_ID = a.ID
WHERE a.CHANNEL = ? AND a.ID < ?
ORDER BY a.ID DESC

优化说明 #

1. 先对点赞、踩表按POST_ID聚合，得到每个帖子的统计总数后再关联主表，彻底避免笛卡尔积导致的重复计数。
2. 使用COALESCE处理NULL值，确保无点赞/踩的帖子返回0而非NULL。
3. 单独通过子查询判断用户互动状态，关联时匹配POST_ID，逻辑更严谨。

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