首页 > sql > 在WHERE子句中使用带有DATE类型的CASE

在WHERE子句中使用带有DATE类型的CASE (Using CASE with DATE Type in WHERE clause)

问题

我想使用CASE与Date类型WHERE条款,所以当@birthFrom或者@birthToNULL返回所有记录。

DECLARE @birthFrom DATE= NULL --'19941012'
DECLARE @birthTo DATE= NULL --'20101012'

SELECT  *
FROM    dbo.bbmf
WHERE   birth BETWEEN ( CASE @birthFrom
                      WHEN NULL THEN birth
                      ELSE @birthFrom
                    END )
          AND     ( CASE @birthTo
                      WHEN NULL THEN birth
                      ELSE @birthTo
                    END )

我的问题是:当我执行上面的代码时,没有选择记录

有什么建议吗?

解决方法

case不工作的原因null是不等于null。表达式的null = null计算结果为unknown。例如:

select case null when null then 1 else 2 end
-->
2

你可以让它像它一样工作case when x is null。注意使用is相反=

declare @x int = null
select case when @x is null then 1 else 2 end
-->
1

有关更多详细信息,请参阅Wikipedia关于三值逻辑的文章

一种更简单的方法是完全省略案例:

where   (@birthFrom is null or @birthFrom <= birth)
        and (@birthTo is null or birth <= @birthTo)

问题

I want to use CASE with DATE Type in WHERE clause, so when @birthFrom or @birthTo is NULL return all record.

DECLARE @birthFrom DATE= NULL --'19941012'
DECLARE @birthTo DATE= NULL --'20101012'

SELECT  *
FROM    dbo.bbmf
WHERE   birth BETWEEN ( CASE @birthFrom
                      WHEN NULL THEN birth
                      ELSE @birthFrom
                    END )
          AND     ( CASE @birthTo
                      WHEN NULL THEN birth
                      ELSE @birthTo
                    END )

My problem is: when I execute the above code there is no record selected

Any suggestion?

解决方法

The reason your case doesn't work is that null is not equal to null. The expression null = null evaluates to unknown. For example:

select case null when null then 1 else 2 end
-->
2

You could make it work like case when x is null. Note the use of is as opposed to =:

declare @x int = null
select case when @x is null then 1 else 2 end
-->
1

For more details, see the Wikipedia article on three valued logic.

A simpler way is to omit the case altogether:

where   (@birthFrom is null or @birthFrom <= birth)
        and (@birthTo is null or birth <= @birthTo)
相似信息