英文:
SQL where clause with multiple criteria in BigQuery?
问题
SELECT *
FROM data
WHERE Operational_End_Date IS NULL OR Operational_End_Date > '2022-08-01'
英文:
Struggling a bit with this seemingly easy SQL query. How can I query a table so that it returns values that are either NULL OR after a certain date? Here's what I have so far:
SELECT *
FROM data
AND Operational_End_Date IN (NULL, > '2022-08-01')
Thanks!
答案1
得分: 0
只需使用 OR：
select *
from data
where operational_end_date is null
or operational_end_date > date '2022-08-01'
注意：假设 operational_end_date 的数据类型是 date，因为它的名称似乎暗示了这一点，您应该将其与字面日期进行比较，而不是字符串。
您还可以使用 coalesce 来表达，如下所示：
select *
from data
where coalesce(operational_end_date, date '9999-12-31') > date '2022-08-01'
思路是将 null 值转换为一个遥远的未来日期（在这里，'9999-12-31' 是BQ支持的最大日期），以使它们通过不等式条件。
英文:
Just use OR:
select *
from data
where operational_end_date is null
or operational_end_date > date '2022-08-01'
Note: assuming that operational_end_date is of the date datatype as its name seems to imply, you would compare it against a literal date rather than a string.
You could also phrase it with coalesce, as in:
select *
from data
where coalesce(operational_end_date, date '9999-12-31') > date '2022-08-01'
The idea is to turn null values to a far future date (here, '9999-12-31' is the greatest date that BQ supports), so they pass the inequality condition.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论