如何修改SQL查询以显示用户每日发帖数（含零发帖情况）

要实现这个需求，核心是先构建所有存在发帖记录的日期与所有用户的完整组合，再基于该组合关联帖子数据统计数量，具体实现如下：

完整查询语句 #

SELECT
    dates.created_at,
    users.name,
    COUNT(posts.id) AS posts_
FROM (SELECT DISTINCT created_at FROM posts) AS dates
CROSS JOIN users
LEFT JOIN posts
    ON posts.user_id = users.id 
    AND posts.created_at = dates.created_at
GROUP BY dates.created_at, users.name
ORDER BY dates.created_at, users.name

关键步骤说明 #

1. 提取所有有发帖的日期
   用SELECT DISTINCT created_at FROM posts获取posts表中所有出现过的日期（去重），确保只包含有发帖行为的日期。
2. 生成日期-用户的完整组合
   通过CROSS JOIN将日期集合与所有用户做交叉关联，得到每个用户对应每个有发帖记录日期的组合，这一步是确保没发帖用户也能出现在对应日期中的核心。
3. 左连接帖子表统计数量
   使用LEFT JOIN关联posts表，关联条件同时匹配用户ID和日期，这样即使用户当日无发帖，组合记录也会被保留；COUNT(posts.id)会自动忽略NULL值，无发帖的记录会统计为0。

测试结果 #

用你提供的测试数据执行该查询，会得到如下结果：

created_at  name   posts_
2022/01/01  user1  2
2022/01/01  user2  1
2022/01/01  user3  0
2022/01/02  user1  1
2022/01/02  user2  1
2022/01/02  user3  0

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