我正在尝试为来自 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 子句中实现这一点
P粉0837850142023-09-12 16:42:13
如果无法查看完整的查询,就不可能提出最佳解决方案。最简单的解决方案是将标准添加为 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' );
P粉7181655402023-09-12 13:39:57
您可以在 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'