MySQL 如何按双表同名字段created_date统计用户每日提问回答数

你之前写的查询存在三个核心偏差，完全不符合需求：

• 关联逻辑错位：通过q.id = a.question_id做隐式内连接，本质是统计「该用户发的提问下关联了多少条回答」，既没有筛选answer表的user_id来统计用户自己发的回答，也会漏掉没有任何回答的提问
• 计数逻辑失真：因为一个提问可以对应多条回答，关联后会出现行膨胀，直接计数会把重复行算进去，结果不准
• 结果覆盖不全：内连接只会保留两边都能匹配上的行，没有行为的日期根本不会出现在最终结果里，满足不了“无行为时计数为0”的要求

核心思路是分步拆解需求，避开复杂的表直接关联：

1. 先分别做两个独立的基础统计，各自算出该用户每日的提问数、每日的回答数（这部分是你已经掌握的单表分组查询逻辑）
2. 确定最终结果需要覆盖的日期范围，作为结果的基础行
3. 把两个统计结果按日期匹配到基础行上，匹配不到的计数用IFNULL补0，最后按日期排序即可

场景1：只展示用户有行为的日期（和你给出的user_id=2示例匹配） #

如果你不需要展示完全无行为的空日期，只需要把两个统计结果中涉及的所有日期去重合并，作为基础行即可，完整可运行SQL如下：

SELECT 
  base.stat_date AS only_date,
  IFNULL(q_stat.questions, 0) AS questions,
  IFNULL(a_stat.answers, 0) AS answers
FROM (
  -- 生成日期集合：合并提问、回答涉及的所有日期并去重
  SELECT DISTINCT stat_date FROM (
    SELECT CAST(created_date AS DATE) AS stat_date 
    FROM question 
    WHERE user_id = 2 -- 替换为需要统计的用户ID
  ) AS q_dates
  UNION
  SELECT DISTINCT stat_date FROM (
    SELECT CAST(created_date AS DATE) AS stat_date 
    FROM answer 
    WHERE user_id = 2 -- 和上面保持同一个用户ID
  ) AS a_dates
) AS base
-- 关联匹配每日提问数
LEFT JOIN (
  SELECT 
    CAST(created_date AS DATE) AS stat_date,
    COUNT(*) AS questions
  FROM question
  WHERE user_id = 2
  GROUP BY stat_date
) AS q_stat ON base.stat_date = q_stat.stat_date
-- 关联匹配每日回答数
LEFT JOIN (
  SELECT 
    CAST(created_date AS DATE) AS stat_date,
    COUNT(*) AS answers
  FROM answer
  WHERE user_id = 2
  GROUP BY stat_date
) AS a_stat ON base.stat_date = a_stat.stat_date
ORDER BY base.stat_date ASC;

这个SQL跑出来的结果和你给出的user_id=2预期输出完全一致：当天只有回答没有提问时，提问计数自动补0；两边都有行为时取对应统计值；只有提问没有回答时，回答计数自动补0。

场景2：展示连续时间范围的所有日期（和你最开始提的“近30天每日统计”需求匹配） #

如果你需要覆盖指定时间范围内的每一天，哪怕当天完全没有任何行为也要补0占位，可以把上面的基础日期集合替换为连续日期生成逻辑，比如近30天的全量日期：

SELECT 
  base.stat_date AS date,
  IFNULL(q_stat.questions, 0) AS questions,
  IFNULL(a_stat.answers, 0) AS answers
FROM (
  -- 生成近30天的连续日期序列
  SELECT 
    DATE_SUB(CURDATE(), INTERVAL seq DAY) AS stat_date
  FROM (
    SELECT a.N + b.N*10 AS seq
    FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2) b
  ) AS seq_table
  WHERE seq BETWEEN 0 AND 29 -- 调整区间即可修改时间范围，比如要查60天就改成BETWEEN 0 AND 59
) AS base
-- 关联匹配近30天内的每日提问数
LEFT JOIN (
  SELECT 
    CAST(created_date AS DATE) AS stat_date,
    COUNT(*) AS questions
  FROM question
  WHERE user_id = 1 -- 替换为目标用户ID
    AND created_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) -- 匹配30天的时间范围
  GROUP BY stat_date
) AS q_stat ON base.stat_date = q_stat.stat_date
-- 关联匹配近30天内的每日回答数
LEFT JOIN (
  SELECT 
    CAST(created_date AS DATE) AS stat_date,
    COUNT(*) AS answers
  FROM answer
  WHERE user_id = 1 -- 和上面保持同一个用户ID
    AND created_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) -- 同步时间范围
  GROUP BY stat_date
) AS a_stat ON base.stat_date = a_stat.stat_date
ORDER BY base.stat_date ASC;

这个SQL会输出近30天的每一天，哪怕当天用户既没有提问也没有回答，也会显示计数为0的行，和你最开始给出的预期输出格式完全匹配。

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