SQL统计当月记录问题：无本年记录取上年同期，过季后返回0

核心需求梳理 #

1. 当前月份为4月及以后时，直接返回0
2. 当前月份为1-3月时：
   • 优先统计本年对应月份的user_activity记录数
   • 若本年该月无记录，统计上年同期的记录数

原代码问题分析 #

你把CASE写在WHERE子句里的逻辑是错误的：WHERE里的CASE是对每一条记录单独判断，只要CASE返回true，这条记录就会被计入统计。比如当本年有目标月份记录时，本年的记录会触发第一个WHEN返回true，而上年同期的记录会触发第二个WHEN返回true，最终两类记录都被统计，导致总数是两者之和，这和你预期的“二选一”逻辑不符。

正确SQL写法 #

写法一：分步判断（逻辑清晰，适合新手理解）

SELECT
    CASE
        -- 4月及以后直接返回0
        WHEN MONTH(CURDATE()) >= 4 THEN 0
        ELSE
            -- 判断本年对应月份是否有记录，有则取本年数，无则取上年数
            CASE
                WHEN (SELECT COUNT(*) 
                      FROM user_activity 
                      WHERE YEAR(creation_date) = YEAR(CURDATE()) 
                        AND MONTH(creation_date) = MONTH(CURDATE())) > 0
                THEN (SELECT COUNT(*) 
                      FROM user_activity 
                      WHERE YEAR(creation_date) = YEAR(CURDATE()) 
                        AND MONTH(creation_date) = MONTH(CURDATE()))
                ELSE (SELECT COUNT(*) 
                      FROM user_activity 
                      WHERE YEAR(creation_date) = YEAR(CURDATE()) - 1 
                        AND MONTH(creation_date) = MONTH(CURDATE()))
            END
    END AS ticket_count;

写法二：用变量简化（减少重复代码）

-- 先定义当前年份和月份变量
SET @curr_year = YEAR(CURDATE());
SET @curr_month = MONTH(CURDATE());

SELECT
    CASE
        WHEN @curr_month >= 4 THEN 0
        ELSE
            CASE
                WHEN (SELECT COUNT(*) FROM user_activity WHERE YEAR(creation_date) = @curr_year AND MONTH(creation_date) = @curr_month) > 0
                THEN (SELECT COUNT(*) FROM user_activity WHERE YEAR(creation_date) = @curr_year AND MONTH(creation_date) = @curr_month)
                ELSE (SELECT COUNT(*) FROM user_activity WHERE YEAR(creation_date) = @curr_year - 1 AND MONTH(creation_date) = @curr_month)
            END
    END AS ticket_count;

补充说明 #

如果需要指定任意月份（而非当前月份），只需把MONTH(CURDATE())替换成目标月份数字即可，比如要统计2月数据，就把所有MONTH(CURDATE())改成2，同时保留“4月及以后返回0”的逻辑（如果需要的话）。

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