MySQL使用BETWEEN与NULL进行比较 [英] MySQL using BETWEEN comparison with NULL

问题描述

我有一个查询，条件如下:

I have a query with a where condition like so:

WHERE A.event_date BETWEEN B.start_date AND B.end_date

复杂度是，如果B.start_date为NULL，则意味着从远古时代开始，同样，B.end_date为NULL，则表示当前.因此，我仍然想选择例如A.event_date > B.start_date和B.end_date为NULL的行.

The complexity is that if B.start_date is NULL, it means from time immemorial, similarly B.end_date is NULL, it means the present. So I still want to select a row where A.event_date > B.start_date and B.end_date is NULL, for example.

实现这一目标的漫长方法是

The long-winded way to do this is

WHERE A.event_date BETWEEN B.start_date AND B.end_date
   OR (A.event_date > B.start_date AND B.end_date IS NULL)
   OR (B.start_date IS NULL AND A.event_date < B.end_date)

有没有更优雅的解决方案(特别是因为在一个查询中我在多个条件之间有多个类似的条件)

Is there a more elegant solution (especially since in one query I have multiple between condition like that)

推荐答案

IFNULL在这里可能会有所帮助:

IFNULL might help out here:

WHERE A.event_date BETWEEN IFNULL(B.start_date,"1900-01-01") 
                   AND IFNULL(B.end_date,now());

这篇关于MySQL使用BETWEEN与NULL进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！