如何对两张表分别分组统计后关联，得到含转发、点赞计数的结果表

你之前使用UNION的逻辑是合并两个查询的结果行，无法实现按post_id对齐拼接计数列的需求，需要通过表关联的方式实现预期结果。

方案1：使用全外连接（支持FULL OUTER JOIN的数据库适用，如PostgreSQL、MySQL 8.0+、Oracle等） #

SELECT
  COALESCE(r.post_id, l.post_id) AS post_id,
  COALESCE(r.retweet_count, 0) AS retweet_count,
  COALESCE(l.likes_count, 0) AS likes_count
FROM (
  -- 先统计每个post的转发量
  SELECT post_id, COUNT(*) AS retweet_count
  FROM retweet
  GROUP BY post_id
) r
FULL OUTER JOIN (
  -- 再统计每个post的点赞量
  SELECT post_id, COUNT(*) AS likes_count
  FROM likes
  GROUP BY post_id
) l ON r.post_id = l.post_id;

其中COALESCE函数用于取第一个非空值，当某个post_id只存在于单张表中时，另一张表对应的计数字段会返回空，用该函数可以将空值转为0，符合你预期的输出格式。

方案2：低版本数据库兼容方案（不支持FULL OUTER JOIN时使用，如MySQL 5.x） #

先通过UNION拿到所有去重的post_id，再分别左联两个统计结果：

SELECT
  p.post_id,
  COALESCE(r.retweet_count, 0) AS retweet_count,
  COALESCE(l.likes_count, 0) AS likes_count
FROM (
  -- 取两张表所有不重复的post_id
  SELECT post_id FROM retweet
  UNION
  SELECT post_id FROM likes
) p
LEFT JOIN (
  SELECT post_id, COUNT(*) AS retweet_count
  FROM retweet
  GROUP BY post_id
) r ON p.post_id = r.post_id
LEFT JOIN (
  SELECT post_id, COUNT(*) AS likes_count
  FROM likes
  GROUP BY post_id
) l ON p.post_id = l.post_id;

生成新表方法 #

如果需要直接将查询结果保存为永久新表，在SELECT语句前增加CREATE TABLE 新表名 即可，示例：

CREATE TABLE post_statistics AS
-- 把上面你选择的SELECT查询语句完整放在此处

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