SQL多表关联查询：过滤特定账号时NULL值显示为0的问题

问题原因 #

• 使用WHERE users_account = 10时，LEFT JOIN生成的无匹配用户的行（对应无活跃用户的日期）会被WHERE条件过滤，导致这些日期无法显示。
• 使用LEFT JOIN users ON activity_user = users_id AND users_account = 10时，activity表中不属于目标账号的用户记录仍会被保留，仅users表字段为NULL，COUNT(activity_user)仍会统计这些记录，因此过滤失效。

方案一：统计时过滤目标账号用户 #

修改统计逻辑，仅计算属于目标账号的活跃用户，同时保留所有日期：

WITH RECURSIVE Dates AS (
    SELECT CURDATE() - INTERVAL 6 DAY AS date
    UNION ALL
    SELECT date + INTERVAL 1 DAY
    FROM Dates
    WHERE date < CURDATE()
) 
SELECT 
    CASE 
        WHEN "weekly" = "biannually" THEN DATE_SUB(date, INTERVAL (DAYOFWEEK(Dates.date) - 1) DAY) 
        WHEN "weekly" = "yearly" THEN DATE_SUB(date, INTERVAL (DAYOFMONTH(Dates.date) - 1) DAY) 
        WHEN "weekly" = "daily" THEN DATE_FORMAT(date, "%Y-%m-%d %H:00:00") 
    ELSE 
        date
    END AS id, 
COALESCE(COUNT(DISTINCT CASE WHEN users_account = 10 THEN activity_user END), 0) AS users
FROM Dates 
    LEFT JOIN activity ON date = activity_date
    LEFT JOIN users ON activity_user = users_id
GROUP BY id
ORDER BY id ASC;

说明：通过CASE WHEN筛选出目标账号的用户ID，COUNT(DISTINCT)避免同一用户当日多次活跃重复计数；无符合条件用户时，CASE返回NULL，COUNT会忽略该值，COALESCE将结果转为0。

方案二：提前过滤目标账号用户 #

先从users表筛选出目标账号的用户，再与activity表关联，仅保留目标账号的活跃记录：

WITH RECURSIVE Dates AS (
    SELECT CURDATE() - INTERVAL 6 DAY AS date
    UNION ALL
    SELECT date + INTERVAL 1 DAY
    FROM Dates
    WHERE date < CURDATE()
) 
SELECT 
    CASE 
        WHEN "weekly" = "biannually" THEN DATE_SUB(date, INTERVAL (DAYOFWEEK(Dates.date) - 1) DAY) 
        WHEN "weekly" = "yearly" THEN DATE_SUB(date, INTERVAL (DAYOFMONTH(Dates.date) - 1) DAY) 
        WHEN "weekly" = "daily" THEN DATE_FORMAT(date, "%Y-%m-%d %H:00:00") 
    ELSE 
        date
    END AS id, 
COALESCE(COUNT(DISTINCT filtered_users.users_id), 0) AS users
FROM Dates 
    LEFT JOIN activity ON date = activity_date
    LEFT JOIN (
        SELECT users_id FROM users WHERE users_account = 10
    ) AS filtered_users ON activity_user = filtered_users.users_id
GROUP BY id
ORDER BY id ASC;

说明：通过子查询filtered_users提前获取目标账号的用户ID，仅匹配该范围内的活跃记录；无匹配记录时filtered_users.users_id为NULL，COUNT会忽略该值，COALESCE确保无数据时显示0。

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