Ich möchte Datensätze ausschließen, die nicht in den Wertebereich fallen, der in der Case-When-Anweisung – SQL/MySQL – angegeben ist

Question

Ich versuche, einen saisonalen Bereich für ein bestimmtes Datum aus der Tabelle DTBL_SCHOOL_DATES festzulegen. Unten ist meine Logik, die die Jahreszeiten basierend auf dem Jahr und seiner Region innerhalb eines bestimmten Bereichs festlegt.

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"

Aber wenn die Daten nicht innerhalb des in der Logik angegebenen Bereichs liegen, möchte ich, dass sie ignoriert werden. AKA-Werte „außerhalb des Bereichs“ sollten ausgeschlossen werden. Ich habe versucht, FTBL_TEST_SCORES.test_admin_period not null zu verwenden, und da kein Wert in der Datenbank null ist, funktionieren sie nicht.

Werte, die nicht im Bereich liegen, sollen vom Ergebnis ausgeschlossen werden, wie kann ich das in der where-Klausel erreichen

Ich habe versucht, die Nutzung von Alias ​​einzuschränken, aber es hat nicht funktioniert. Ich bin mir nicht sicher, ob es möglich ist, einem bestimmten Feld in einer Case-Anweisung einen Wert zuzuweisen, z. B. Case when 'a' then field ='B' end

Werte, die nicht im Bereich liegen, sollen vom Ergebnis ausgeschlossen werden, wie kann ich das in der where-Klausel erreichen

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'