MySQL中如何正确合并多个SELECT实现帖子多维度数据关联查询

你之前的写法报错或统计结果错误的核心原因是统计子查询没有和外层当前查询的主帖ID关联，直接全局GROUP BY会返回所有帖子的统计结果集合，无法和单条主帖记录一一对应。

以下是两种可直接使用的解决方案，均适配Node.js mysql模块的执行要求：

方案1：关联子查询写法（写法简单，适合数据量较小的场景） #

直接在SELECT中嵌套关联了外层主帖ID的统计子查询：

SELECT 
  posts.username, 
  posts.time, 
  cat.cat_name, 
  posts.title, 
  posts.content, 
  posts.reply, 
  posts.user_file, 
  posts.audio,
  -- 统计回复数：统计reply字段等于当前主帖ID的记录数
  IFNULL((SELECT COUNT(*) FROM posts AS p_reply WHERE p_reply.reply = posts.id), 0) AS comments,
  -- 统计浏览量：取当前主帖对应social记录的最大views值
  IFNULL((SELECT MAX(s.views) FROM social AS s WHERE s.post_id = posts.id), 0) AS views,
  -- 统计点赞数：取当前主帖对应social最新记录的likes值
  IFNULL((SELECT s.likes FROM social AS s WHERE s.post_id = posts.id ORDER BY s.id DESC LIMIT 1), 0) AS likes
FROM posts 
LEFT JOIN user on posts.user_id = user.id 
LEFT JOIN cat ON posts.cat_id = cat.id
-- 只查询主帖，若需要同时查询回复可移除该条件
WHERE posts.reply = 0

方案2：预聚合JOIN写法（性能更优，适合数据量较大的生产环境） #

提前将三个统计指标按帖子ID聚合为临时表，再和主表关联，避免逐行执行子查询的性能损耗：

SELECT 
  posts.username, 
  posts.time, 
  cat.cat_name, 
  posts.title, 
  posts.content, 
  posts.reply, 
  posts.user_file, 
  posts.audio,
  IFNULL(reply_stat.comments, 0) AS comments,
  IFNULL(view_stat.views, 0) AS views,
  IFNULL(like_stat.likes, 0) AS likes
FROM posts 
LEFT JOIN user on posts.user_id = user.id 
LEFT JOIN cat ON posts.cat_id = cat.id
-- 关联回复数统计临时表
LEFT JOIN (
  SELECT reply AS post_id, COUNT(*) AS comments 
  FROM posts 
  WHERE reply != 0
  GROUP BY reply
) AS reply_stat ON posts.id = reply_stat.post_id
-- 关联浏览量统计临时表
LEFT JOIN (
  SELECT post_id, MAX(views) AS views 
  FROM social 
  GROUP BY post_id
) AS view_stat ON posts.id = view_stat.post_id
-- 关联点赞数统计临时表
LEFT JOIN (
  SELECT s1.post_id, s1.likes 
  FROM social s1
  INNER JOIN (
    SELECT post_id, MAX(id) AS max_id 
    FROM social 
    GROUP BY post_id
  ) s2 ON s1.post_id = s2.post_id AND s1.id = s2.max_id
) AS like_stat ON posts.id = like_stat.post_id
-- 只查询主帖，若需要同时查询回复可移除该条件
WHERE posts.reply = 0

两种写法返回的结果集都会直接携带comments、views、likes三个统计字段，无需额外做数据对齐处理。

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