我想排除不属于 case when 语句中指定的值范围的记录 - SQL/MySQL

Question

我正在尝试为来自 DTBL_SCHOOL_DATES 表的特定日期设置季节范围。以下是我的逻辑，它根据年份及其地区来设置给定范围内的季节。

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 不为空，并且由于数据库中没有值为空，它们将无法工作。

不在范围内的值应该被排除在结果中，我如何在 where 子句中实现这一点

我尝试使用 Alias 进行限制，但不起作用。 我不确定是否可以将值分配给 case 语句中的特定字段，例如 Case when 'a' then field ='B' end

不在范围内的值应该被排除在结果中，我如何在 where 子句中实现这一点

Answer 1

如果无法查看完整的查询，就不可能提出最佳解决方案。最简单的解决方案是将标准添加为 HAVING 子句：

HAVING `C4630` <> 'NOT IN RANGE'

我对你的日期格式感到困惑。如果这应该是 MySQL 查询，则它们应该采用 yyyy-mm-dd 格式。可以说，即使在支持不明确的本地日期格式的 SQL Server 上，它们也应该采用这种格式。

为什么都是RTRIM和CAST？数据应该以正确的格式存储，并在输入时进行清理，如果无法做到这一点，请定期清理数据，而不是对每个查询都进行清理。

明智的做法是将您的季节移动到它们自己的表中，而不是在查询时定义它们。然后它是一个简单的连接到seasons。我在这里使用了子查询，但希望您能明白：

SELECT
    /* other columns */
    COALESCE(seasons.season, FTBL_TEST_SCORES.test_admin_period) AS `C4630`
FROM all_the_other_tables
LEFT JOIN (

    SELECT 'Bay Area' AS region, 'FALL' AS season, '2021-08-07' AS start, '2021-09-08' AS end UNION ALL
    SELECT 'Bay Area'          , 'WINTER'        , '2021-11-27'         , '2021-12-15'        UNION ALL
    SELECT 'Bay Area'          , 'SPRING'        , '2022-03-04'         , '2022-03-22'        UNION ALL
        
    SELECT 'Central Valley'    , 'FALL'          , '2021-07-31'         , '2021-09-01'        UNION ALL
    SELECT 'Central Valley'    , 'WINTER'        , '2021-11-27'         , '2021-12-15'        UNION ALL
    SELECT 'Central Valley'    , 'SPRING'        , '2022-02-19'         , '2022-03-08'        UNION ALL
        
    SELECT 'Los Angeles'       , 'FALL'          , '2021-08-14'         , '2021-09-15'        UNION ALL
    SELECT 'Los Angeles'       , 'WINTER'        , '2021-11-27'         , '2021-12-15'        UNION ALL
    SELECT 'Los Angeles'       , 'SPRING'        , '2022-03-04'         , '2022-03-22'

) AS seasons
    ON dtbl_school_dates.local_school_year = '2021-2022'
    AND dtbl_schools_ext.region = seasons.region
    AND dtbl_school_dates.date_value BETWEEN seasons.start AND seasons.end
WHERE (
    (dtbl_school_dates.local_school_year = '2021-2022' AND seasons.season IS NOT NULL) OR
    dtbl_school_dates.local_school_year <> '2021-2022'
);

Answer 2

您可以在 WHERE 子句中重复整个 CASE 语句，如下所示：

select
    case
        when x then y 
        when a then b 
        when c then d 
        else 'NOT IN RANGE'
    end as foo
from
    table t 
where 
    case
        when x then y 
        when a then b 
        when c then d 
        else 'NOT IN RANGE'
    end <> 'NOT IN RANGE'

或者您可以使用子查询（或 CTE），如下所示：

select * 
from (
    select
        case
            when x then y 
            when a then b 
            when c then d 
            else 'NOT IN RANGE'
        end as foo
    from
        table t 
) 
where foo <> 'NOT IN RANGE'