使用CASE与WHERE中OR按年份统计USER_POST记录的SQL问题

问题背景 #

现有USER_POST表（标注为USER_WORK）存储用户帖子数据，表结构及数据如下：

//**USER_WORK** table
+----+---------+-----------+--------------+
| id |   name  |  post_id  |     date     |
+----+---------+-----------+--------------+
|  1 | Anthony |     1     |  2017-01-01  |
|  2 | Sage    |     2     |  2017-02-15  |
|  3 | Khloe   |     3     |  2017-06-10  |
|  4 | Anthony |     4     |  2017-08-01  |
|  5 | Khloe   |     5     |  2017-12-09  |
|  6 | Anthony |     6     |  2018-04-27  |
|  7 | Sage    |     7     |  2018-07-29  |
|  8 | Brandon |     8     |  2018-09-13  |
|  9 | Khloe   |     9     |  2018-10-10  |
| 10 | Brandon |    10     |  2018-11-03  |
+----+---------+-----------+--------------+

需统计每个用户2017年、2018年的发帖数量，预期结果：

+-----------+-----------------+-----------------+
| user_name | cnt_data_year_1 | cnt_data_year_2 |
+-----------+-----------------+-----------------+
|  Anthony  |        2        |        1        |
|  Sage     |        1        |        1        |
|  Khloe    |        2        |        1        |
|  Brandon  |        0        |        2        |
+-----------+-----------------+-----------------+

但执行原有SQL后，2017年统计值全为0：

//result with problem
+-----------+-----------------+-----------------+
| user_name | cnt_data_year_1 | cnt_data_year_2 |
+-----------+-----------------+-----------------+
|  Anthony  |        0        |        1        |
|  Sage     |        0        |        1        |
|  Khloe    |        0        |        1        |
|  Brandon  |        0        |        2        |
+-----------+-----------------+-----------------+

错误原因 #

1. 日期范围定义错误：原有SQL中，2017年和2018年的统计条件仅限制在1月份（<=2017-01-31、<=2018-01-31），而表中2017年的有效数据均不在1月，导致这部分数据被排除，统计结果为0。
2. 过滤条件过严：子查询的WHERE子句仅保留了2017年1月和2018年1月的数据，无法覆盖全年的发帖记录。
3. 冗余代码与笔误：子查询中case when up.name is not null then up.name完全可以直接用up.name；同时case语句中存在拼写错误esle（应为else）。

修正后的SQL #

采用条件聚合直接统计，简化逻辑并覆盖全年数据：

SELECT 
    name AS user_name,
    SUM(CASE WHEN YEAR(date) = 2017 THEN 1 ELSE 0 END) AS cnt_data_year_1,
    SUM(CASE WHEN YEAR(date) = 2018 THEN 1 ELSE 0 END) AS cnt_data_year_2
FROM USER_POST
-- 若有其他过滤条件，可在此添加 AND 子句
GROUP BY name;

如果需要保留原有的日期范围写法（而非用YEAR()函数），可调整为：

SELECT 
    name AS user_name,
    SUM(CASE WHEN date >= '2017-01-01' AND date < '2018-01-01' THEN 1 ELSE 0 END) AS cnt_data_year_1,
    SUM(CASE WHEN date >= '2018-01-01' AND date < '2019-01-01' THEN 1 ELSE 0 END) AS cnt_data_year_2
FROM USER_POST
-- 若有其他过滤条件，可在此添加 AND 子句
GROUP BY name;

验证结果 #

执行修正后的SQL，将得到与预期完全一致的统计结果，正确计算每个用户在2017、2018年的发帖数量。

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