Where子句在2个布尔变量上切换大小写

Question

查找一个基于布尔变量执行where子句的查询。

类似于：

select
COUNT(col1)
from
table1
where
(@var1 is not null and col1 > @var1)
and (@var2 is not null and col1 <= @var2);

请注意，上面的查询不能满足我的需要。我基本上想要：

If @var1 == NULL and @var2 == NULL THEN true
If @var1 == NULL and @var2 != NULL THEN col1 <= @var2
If @var1 != NULL and @var2 == NULL THEN col1 > @var1
If @var1 != NULL and @var2 != NULL THEN col1 > @var1 && col1 <= @var2

Answer 1

你好像半途而废了？只需按照开始的方式继续。

WHERE
(@var1 IS NULL AND @var2 AND NULL)
OR (@var1 IS NULL AND @var2 IS NOT NULL AND col2 <= @var2)
OR (@var1 IS NOT NULL AND @var2 IS NULL AND col1 > @var1)
OR (@var1 IS NOT NULL AND @var2 IS NOT NULL AND col1 > @var1 AND col2 <= @var2)

Answer 2

希望能正常工作

where    ( 1= case when @var1 is  null and @var2 is  null then 1 else 0 end )
OR ( case when  @var1 is null and @var2 is not null then col2<=@var2 
end)
OR ( case when @var1 is not null and @var2 is null then col1>@var1 end)
OR ( case when @var1 is not null and @var2 is not null then col1>@var1 
and col2<=@var2 end)