查询各年代电影数量时DECADE列全为NULL的SQL问题排查

核心问题分析 #

• 数据类型不匹配：如果ReleaseDate是DATE/DATETIME类型，直接用数字区间（比如BETWEEN 2010 AND 2019）判断会失效。数据库会把完整日期（如2015-05-20）转换为数值（如20150520），这个数值远大于2019，导致所有CASE条件都不满足，返回NULL。
• 分组逻辑错误：原语句按ReleaseDate分组，会将每个单独的日期作为一组，既不符合“统计每个年代”的需求，也进一步导致CASE判断无法正确匹配年代区间。

修正后的SQL语句 #

情况1：ReleaseDate为DATE/DATETIME类型

SELECT 
    CASE
        WHEN YEAR(ReleaseDate) BETWEEN 2010 AND 2019 THEN '2010 - 2019'
        WHEN YEAR(ReleaseDate) BETWEEN 2000 AND 2009 THEN '2000 - 2009'
        WHEN YEAR(ReleaseDate) BETWEEN 1990 AND 1999 THEN '1990 - 1999'
        WHEN YEAR(ReleaseDate) BETWEEN 1980 AND 1989 THEN '1980 - 1989'
        WHEN YEAR(ReleaseDate) BETWEEN 1970 AND 1979 THEN '1970 - 1979'
        WHEN YEAR(ReleaseDate) BETWEEN 1960 AND 1969 THEN '1960 - 1969'
        ELSE '其他年代'
    END AS DECADE,
    COUNT(Title) AS 电影数量
FROM Film
GROUP BY DECADE
ORDER BY DECADE DESC;

情况2：ReleaseDate为存储年份的数字类型（如INT）

SELECT 
    CASE
        WHEN ReleaseDate BETWEEN 2010 AND 2019 THEN '2010 - 2019'
        WHEN ReleaseDate BETWEEN 2000 AND 2009 THEN '2000 - 2009'
        WHEN ReleaseDate BETWEEN 1990 AND 1999 THEN '1990 - 1999'
        WHEN ReleaseDate BETWEEN 1980 AND 1989 THEN '1980 - 1989'
        WHEN ReleaseDate BETWEEN 1970 AND 1979 THEN '1970 - 1979'
        WHEN ReleaseDate BETWEEN 1960 AND 1969 THEN '1960 - 1969'
        ELSE '其他年代'
    END AS DECADE,
    COUNT(Title) AS 电影数量
FROM Film
GROUP BY DECADE
ORDER BY DECADE DESC;

关键调整说明 #

• 用YEAR()函数提取日期类型中的年份，再做区间判断，这是DATE/DATETIME类型的标准处理方式。
• 将分组字段改为DECADE，确保按年代聚合统计数量，而非单个日期。
• 增加ELSE '其他年代'分支，覆盖不在指定区间内的电影，避免出现NULL行。

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