如何合并两个正常运行的MySQL查询？解决联表后仅返回单条评论问题

问题描述 #

有两个独立运行正常的MySQL查询：

1. 媒体统计查询：针对指定media_id和title返回单行结果，包含media_id、title和share_count（分享统计）。
2. 评论列表查询：针对相同的media_id和title返回所有对应评论的多行结果，包含评论详情及投票统计。

尝试合并两个查询后，仅能返回单条评论（原本应返回所有评论），仅share_count结果正确，需要找到正确的合并方式。

正确合并方法 #

方法1：在SELECT子句中嵌入子查询获取统计数据 #

将媒体统计查询作为子查询，直接嵌入到评论查询的SELECT列表中——因为share_count是针对当前媒体的单一值，每条评论都会带上这个统计值：

SELECT 
    ct.id, 
    ct.comment, 
    ct.comment_parent_id, 
    ct.reported, 
    ct.user_id, 
    ct.user_display_name, 
    ct.avatar, 
    ct.c_date, 
    SUM(vt.vote) AS vote, 
    MAX(vt.user_id = %d) AS user_voted,
    MAX(CASE WHEN vt.user_id = %d THEN vote END) AS user_vote,
    -- 嵌入子查询获取当前媒体的share_count
    (SELECT SUM(DISTINCT sht.share)
     FROM $grouped_media_table mt   
     LEFT JOIN $share_table sht ON sht.media_id = mt.media_id AND sht.title = mt.title
     WHERE mt.media_id = %d AND mt.title = %s) AS share_count,
    ct.media_id,
    ct.title
FROM $comments_table as ct 
LEFT JOIN $votes_table vt on ct.id = vt.comment_id 
WHERE ct.media_id=%d AND ct.title=%s
GROUP BY ct.id
ORDER BY ct.c_date DESC

方法2：将统计查询作为临时表关联到评论查询 #

先预计算媒体统计结果为一个临时表，再通过media_id和title与评论查询进行LEFT JOIN，确保每条评论都关联到正确的统计数据：

SELECT 
    ct.id, 
    ct.comment, 
    ct.comment_parent_id, 
    ct.reported, 
    ct.user_id, 
    ct.user_display_name, 
    ct.avatar, 
    ct.c_date, 
    SUM(vt.vote) AS vote, 
    MAX(vt.user_id = %d) AS user_voted,
    MAX(CASE WHEN vt.user_id = %d THEN vote END) AS user_vote,
    media_stats.media_id,
    media_stats.title,
    media_stats.share_count
FROM $comments_table as ct 
LEFT JOIN $votes_table vt on ct.id = vt.comment_id 
-- 关联预计算的媒体统计临时表
LEFT JOIN (
    SELECT mt.media_id, mt.title,
           SUM(DISTINCT sht.share) as share_count
    FROM $grouped_media_table mt   
    LEFT JOIN $share_table sht ON sht.media_id = mt.media_id AND sht.title = mt.title
    WHERE mt.media_id = %d AND mt.title = %s 
    GROUP BY mt.media_id, mt.title
) AS media_stats ON ct.media_id = media_stats.media_id AND ct.title = media_stats.title
WHERE ct.media_id=%d AND ct.title=%s
GROUP BY ct.id, media_stats.media_id, media_stats.title, media_stats.share_count
ORDER BY ct.c_date DESC

错误原因分析 #

直接将所有表（grouped_media_table、share_table、comments_table、votes_table）一次性JOIN会产生笛卡尔积，导致评论数据被错误聚合；若同时沿用原统计查询的GROUP BY条件（mt.media_id, mt.title），则会将所有评论数据合并为一行，最终仅返回单条评论。先预计算统计结果再关联，就能避免这个问题。

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