带日期筛选的跨关联表条目计数查询（需包含零值）

嗨，我来帮你搞定这个问题！你的核心痛点是加了日期筛选后，那些没有符合条件条目分类的零值统计不见了，还有出现null的情况，对吧？这是因为你把WHERE条件放在了LEFT JOIN之后，直接过滤掉了主表categories里没有匹配到符合日期条件的照片/视频的行——毕竟LEFT JOIN后没匹配到的话，photos.date_added会是NULL，WHERE条件会把这些行直接排除，自然就丢了那些零值分类。

解决方案：把日期筛选移到LEFT JOIN的ON子句里 #

正确的做法是把日期条件嵌入到关联照片/视频表的ON子句中，这样既不会过滤掉主表的分类，又能只统计符合日期要求的条目。另外还要注意两个小细节：一是日期值要加单引号（因为你的日期字段是TEXT类型，不加引号会被当成数值计算，结果完全不对）；二是两个子查询的GROUP BY要统一用categories.category_id，避免关联时出现不匹配的情况。

修改后的完整查询如下：

SELECT
    photos_group.category_id,
    photos_group.title,
    photos_group.num_of_photos,
    COALESCE(videos_group.num_of_videos, 0) AS num_of_videos
FROM (
    SELECT
        categories.category_id,
        categories.title,
        COUNT(photos.photo_id) AS num_of_photos
    FROM categories
    LEFT JOIN photo_categories 
        ON photo_categories.category_id = categories.category_id
    LEFT JOIN photos 
        ON photo_categories.photo_id = photos.photo_id 
        AND photos.date_added >= '2021-01-01' -- 把日期条件移到这里
    GROUP BY categories.category_id, categories.title
) photos_group
LEFT JOIN (
    SELECT
        categories.category_id,
        COUNT(videos.video_id) AS num_of_videos
    FROM categories
    LEFT JOIN video_categories 
        ON video_categories.category_id = categories.category_id
    LEFT JOIN videos 
        ON video_categories.video_id = videos.video_id 
        AND videos.date_added >= '2021-01-01' -- 同理移到这里
    GROUP BY categories.category_id
) videos_group
    ON photos_group.category_id = videos_group.category_id;

关键要点解释： #

1. 日期条件移到ON子句：这样LEFT JOIN只会关联符合日期条件的照片/视频，不会过滤掉主表的分类——哪怕某个分类下没有符合条件的条目，依然会保留该分类，计数为0。
2. COALESCE函数处理null：虽然我们的写法已经能避免大部分null，但为了保险（比如某个分类完全没有视频关联时），用COALESCE(videos_group.num_of_videos, 0)可以把null转换成0，完全符合你要的输出格式。
3. 日期值加单引号：你的date_added是TEXT类型，必须用字符串形式的日期值，否则2021-01-01会被计算成2021-1-1=2019，完全不符合你的筛选需求。

验证结果 #

用你给出的示例数据运行这个查询，会得到你想要的结果：

备注：内容来源于stack exchange，提问作者Jimbot