如何合并查询原创帖子与分享帖子数据？

要实现原创帖子和分享帖子的合并查询并按时间排序，核心思路是用UNION ALL将两个数据集拼接，同时保证两者的输出列完全一致，然后统一排序。下面是具体的实现方案：

完整SQL查询语句 #

SELECT * FROM (
  -- 1. 原创帖子查询部分
  SELECT
    'original' AS post_type,
    c.nome AS author_name,
    c.id AS author_id,
    p.id AS original_post_id,
    p.titulo AS post_title,
    p.data AS post_time,
    pp.foto AS author_avatar,
    COUNT(DISTINCT likes.user) AS likes_count,
    COUNT(DISTINCT comentarios.id) AS comentarios_count,
    CASE WHEN COUNT(DISTINCT l2.user) > 0 THEN 1 ELSE 0 END AS current_user_liked,
    NULL AS sharer_name,
    NULL AS sharer_id
  FROM posts p
  JOIN cadastro c ON p.user = c.id
  LEFT JOIN profile_picture pp ON p.user = pp.user
  LEFT JOIN likes ON likes.post = p.id
  LEFT JOIN comentarios ON comentarios.foto = p.id AND comentarios.delete = 0
  LEFT JOIN likes l2 ON l2.post = p.id AND l2.user = ?
  GROUP BY p.id, c.nome, c.id, p.titulo, p.data, pp.foto

  UNION ALL

  -- 2. 分享帖子查询部分
  SELECT
    'shared' AS post_type,
    original_c.nome AS author_name,
    original_c.id AS author_id,
    original_p.id AS original_post_id,
    original_p.titulo AS post_title,
    s.data AS post_time,
    original_pp.foto AS author_avatar,
    COUNT(DISTINCT likes.user) AS likes_count,
    COUNT(DISTINCT comentarios.id) AS comentarios_count,
    CASE WHEN COUNT(DISTINCT l2.user) > 0 THEN 1 ELSE 0 END AS current_user_liked,
    sharer_c.nome AS sharer_name,
    sharer_c.id AS sharer_id
  FROM share s
  JOIN cadastro sharer_c ON s.user = sharer_c.id
  JOIN posts original_p ON s.share_post_id = original_p.id
  JOIN cadastro original_c ON original_p.user = original_c.id
  LEFT JOIN profile_picture original_pp ON original_p.user = original_pp.user
  LEFT JOIN likes ON likes.post = original_p.id
  LEFT JOIN comentarios ON comentarios.foto = original_p.id AND comentarios.delete = 0
  LEFT JOIN likes l2 ON l2.post = original_p.id AND l2.user = ?
  GROUP BY s.id, original_c.nome, original_c.id, original_p.id, original_p.titulo, s.data, original_pp.foto, sharer_c.nome, sharer_c.id
) AS combined_posts
ORDER BY post_time DESC
LIMIT 20;

关键细节说明 #

1. 使用UNION ALL而非UNION：
   UNION会自动去重，而我们需要保留所有原创和分享的动态（即使内容重复），所以用UNION ALL效率更高且符合需求。

2. 区分帖子类型：
   新增post_type字段，值为original或shared，方便前端展示时区分是原创内容还是分享内容（比如显示“[分享] XXX转发了YYY的帖子”）。

3. 时间排序逻辑：
   原创帖子用自身的发布时间p.data，分享帖子用分享操作的时间s.data，这样排序后能保证最新的动态（无论是原创还是分享）排在最前面。

4. 互动数据统计：
   所有点赞、评论统计都是基于原帖子的——因为分享是转发原内容，互动行为应该归属原帖，这样数据才准确。

5. 当前用户点赞状态：
   用CASE语句将原来的count2转化为current_user_liked（1表示已点赞，0表示未点赞），语义更清晰，前端处理更方便。

6. 分组注意事项：
   分组时要包含所有非聚合字段，避免触发MySQL的ONLY_FULL_GROUP_BY模式报错。原创帖子按posts.id分组，分享帖子按share.id分组，确保每条动态独立统计。

额外优化建议 #

• 如果profile_picture表中一个用户存在多条头像记录，建议添加条件取最新的头像（比如pp.id = (SELECT MAX(id) FROM profile_picture WHERE user = p.user)），避免出现重复的帖子数据。
• 如果需要支持用户分享时添加自定义文字，可以给share表新增content字段，并在查询中加入该字段，丰富分享内容。
• 确保?占位符传入的是当前登录用户的ID，用来判断该用户是否点赞过对应帖子。

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