如何在MySQL中排除CASE WHEN语句中不在指定范围的记录？

问题背景 #

已编写CASE WHEN逻辑为DTBL_SCHOOL_DATES表的日期按学年和地区分配季节标签，当2021-2022学年的日期不在指定范围时会返回'NOT IN RANGE'，需要在WHERE子句中排除这类记录。原逻辑代码如下：

CASE 
    WHEN RTRIM(dtbl_school_dates.local_school_year) = '2021-2022' THEN 
        CASE 
            WHEN RTRIM(dtbl_schools_ext.region) = 'Bay Area' AND 
                CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '08/07/2021' and '09/08/2021' THEN 'FALL'
            WHEN RTRIM(dtbl_schools_ext.region) = 'Bay Area' AND 
                CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '11/27/2021' and '12/15/2021' THEN 'WINTER'
            WHEN RTRIM(dtbl_schools_ext.region) = 'Bay Area' AND 
                CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '03/04/2022' and '03/22/2022' THEN 'SPRING'
            WHEN RTRIM(dtbl_schools_ext.region) = 'Central Valley' AND 
                CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '07/31/2021' and '09/01/2021' THEN 'FALL'
            WHEN RTRIM(dtbl_schools_ext.region) = 'Central Valley' AND 
                CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '11/27/2021' and '12/15/2021' THEN 'WINTER'
            WHEN RTRIM(dtbl_schools_ext.region) = 'Central Valley' AND 
                CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '02/19/2022' and '03/08/2022' THEN 'SPRING'
            WHEN RTRIM(dtbl_schools_ext.region) = 'Los Angeles' AND 
                CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '08/14/2021' and '09/15/2021' THEN 'FALL'
            WHEN RTRIM(dtbl_schools_ext.region) = 'Los Angeles' AND 
                CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '11/27/2021' and '12/15/2021' THEN 'WINTER'
            WHEN RTRIM(dtbl_schools_ext.region) = 'Los Angeles' AND 
                CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '03/04/2022' and '03/22/2022' THEN 'SPRING' 
            ELSE 'NOT IN RANGE' 
        END 
    ELSE FTBL_TEST_SCORES.test_admin_period 
END AS "C4630"

之前尝试用FTBL_TEST_SCORES.test_admin_period IS NOT NULL（该字段无空值）或直接引用别名过滤均无效，核心原因是SQL执行顺序为WHERE先于SELECT，WHERE子句无法直接引用SELECT中定义的字段别名。

解决方案 #

方法1：将有效范围逻辑复制到WHERE子句 #

直接在WHERE中判断记录是否属于有效范围，保留非2021-2022学年的记录，以及2021-2022学年且在指定季节范围内的记录：

WHERE 
    -- 保留非2021-2022学年的所有记录
    RTRIM(dtbl_school_dates.local_school_year) != '2021-2022'
    -- 保留2021-2022学年且符合季节范围的记录
    OR (
        RTRIM(dtbl_school_dates.local_school_year) = '2021-2022'
        AND (
            (RTRIM(dtbl_schools_ext.region) = 'Bay Area' AND CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '08/07/2021' AND '09/08/2021')
            OR (RTRIM(dtbl_schools_ext.region) = 'Bay Area' AND CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '11/27/2021' AND '12/15/2021')
            OR (RTRIM(dtbl_schools_ext.region) = 'Bay Area' AND CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '03/04/2022' AND '03/22/2022')
            OR (RTRIM(dtbl_schools_ext.region) = 'Central Valley' AND CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '07/31/2021' AND '09/01/2021')
            OR (RTRIM(dtbl_schools_ext.region) = 'Central Valley' AND CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '11/27/2021' AND '12/15/2021')
            OR (RTRIM(dtbl_schools_ext.region) = 'Central Valley' AND CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '02/19/2022' AND '03/08/2022')
            OR (RTRIM(dtbl_schools_ext.region) = 'Los Angeles' AND CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '08/14/2021' AND '09/15/2021')
            OR (RTRIM(dtbl_schools_ext.region) = 'Los Angeles' AND CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '11/27/2021' AND '12/15/2021')
            OR (RTRIM(dtbl_schools_ext.region) = 'Los Angeles' AND CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '03/04/2022' AND '03/22/2022')
        )
    )

方法2：用CTE/子查询先计算字段再过滤 #

通过CTE（公共表表达式）或子查询先生成包含"C4630"字段的中间结果，再在外层过滤掉'NOT IN RANGE'的记录：

WITH scored_dates AS (
    SELECT 
        -- 替换为你实际需要查询的字段
        dtbl_school_dates.*,
        dtbl_schools_ext.*,
        FTBL_TEST_SCORES.*,
        CASE 
            WHEN RTRIM(dtbl_school_dates.local_school_year) = '2021-2022' THEN 
                CASE 
                    WHEN RTRIM(dtbl_schools_ext.region) = 'Bay Area' AND CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '08/07/2021' and '09/08/2021' THEN 'FALL'
                    WHEN RTRIM(dtbl_schools_ext.region) = 'Bay Area' AND CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '11/27/2021' and '12/15/2021' THEN 'WINTER'
                    WHEN RTRIM(dtbl_schools_ext.region) = 'Bay Area' AND CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '03/04/2022' and '03/22/2022' THEN 'SPRING'
                    WHEN RTRIM(dtbl_schools_ext.region) = 'Central Valley' AND CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '07/31/2021' and '09/01/2021' THEN 'FALL'
                    WHEN RTRIM(dtbl_schools_ext.region) = 'Central Valley' AND CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '11/27/2021' and '12/15/2021' THEN 'WINTER'
                    WHEN RTRIM(dtbl_schools_ext.region) = 'Central Valley' AND CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '02/19/2022' and '03/08/2022' THEN 'SPRING'
                    WHEN RTRIM(dtbl_schools_ext.region) = 'Los Angeles' AND CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '08/14/2021' and '09/15/2021' THEN 'FALL'
                    WHEN RTRIM(dtbl_schools_ext.region) = 'Los Angeles' AND CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '11/27/2021' and '12/15/2021' THEN 'WINTER'
                    WHEN RTRIM(dtbl_schools_ext.region) = 'Los Angeles' AND CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '03/04/2022' and '03/22/2022' THEN 'SPRING' 
                    ELSE 'NOT IN RANGE' 
                END 
            ELSE FTBL_TEST_SCORES.test_admin_period 
        END AS "C4630"
    FROM DTBL_SCHOOL_DATES
    -- 补充你的表关联条件
    JOIN DTBL_SCHOOLS_ext ON dtbl_school_dates.school_id = dtbl_schools_ext.school_id
    JOIN FTBL_TEST_SCORES ON dtbl_school_dates.date_value = FTBL_TEST_SCORES.test_date
)
SELECT * FROM scored_dates WHERE "C4630" != 'NOT IN RANGE';

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