在情况下以一些无效的值进行了条件

Question

下面的简化代码不会在field1中使用null返回行（field2，field3同一问题）。我可以在平等符号之后更改任何内容，如果需要，可以更改平等符号，但是最多的WHERE field1的所有内容都应保持不变。

在同一情况下，我还如何拉is null行？实际查询很大，已经在三个begin..end块中，因此我真的不想通过将3个块乘以更多来使其复杂化。对于（全部）我想要所有行，对于（x），我想要特定的值以及nulls和数据等于变量中的内容。

declare @var1 varchar(100)
declare @var2 varchar(100)
declare @var3 varchar(100)
set @var1 = '(ALL)'
set @var2 = '(x)'
set @var3 = 'data'
SELECT *
FROM table 
WHERE
    field1 = CASE @var1 WHEN '(ALL)' THEN field1 
                        WHEN '(x)' THEN (x & nulls) 
                        ELSE @var1 
             END 
    AND field2 = CASE @var2 WHEN '(ALL)' THEN field2 
                            WHEN '(x)' THEN (x & nulls) 
                            ELSE @var2 
                 END 
    AND field3 = CASE @var3 WHEN '(ALL)' THEN field3 
                            WHEN '(x)' THEN (x & nulls) 
                            ELSE @var3 
                 END 

我真的需要将整个线路的整体示例，没有办法做到这一点吗？

WHERE CASE @var1 WHEN '(x)' THEN field1 = @var1 or field1 is null

编辑：我开发了一个工作解决方案！问题是子句需要分配的地方，它不仅是直接情况，因此我们将实现伪true/false分配。为 @var2/field2和 @var3/field3使用类似的代码。希望这种解决方案将来对他人有所帮助。谢谢大家这个很棒的网站！

SELECT * FROM table WHERE
1 = CASE WHEN @var1 = '(ALL)' THEN 1
         WHEN @var1 = '(x)' AND (field1 = @var1 OR field1 IS NULL) THEN 1
         WHEN @var1 = field1 THEN 1
         ELSE 0 END

编辑：如果您真的想将其凝结：（ field1 = @var1是在下一个情况下按照条件来照顾的）

SELECT * FROM table WHERE
1 = CASE WHEN @var1 = '(ALL)' THEN 1
         WHEN @var1 = '(x)' AND field1 IS NULL THEN 1
         WHEN @var1 = field1 THEN 1
         ELSE 0 END

Answer 1

将您的WHERE子句重写为这样的东西：

WHERE
    (@var1 in ('(ALL)','(x)') OR field1 = @var1)
AND (@var2 in ('(ALL)','(x)') OR field2 = @var2)
AND (@var3 in ('(ALL)','(x)') OR field3 = @var3)

Answer 2

基于先前的答案和评论，您正在寻找这样的东西：

SELECT *
FROM table 
WHERE
(@var1 = '(ALL)' OR (@var1 ='(x)' AND IsNull(field1, '(x)') = @var1) OR field1 = @var1)
AND (@var2 = '(ALL)' OR (@var2 ='(x)' AND IsNull(field2, '(x)') = @var2) OR field2 = @var2)
AND (@var3 = '(ALL)' OR (@var3 ='(x)' AND IsNull(field3, '(x)') = @var3) OR field3 = @var3)
AND ...

如果给定的" x"可以是您需要这样的任何字符序列：

SELECT *
FROM table 
WHERE
(@var1 = '(ALL)' OR (@var1 like '(%)' AND IsNull(field1, SUBSTRING(@var1,2,LEN(@var1)-2)) = SUBSTRING(@var1,2,LEN(@var1)-2)) OR field1 = @var1)
AND (@var2 = '(ALL)' OR (@var2 like '(%)' AND IsNull(field2, SUBSTRING(@var2,2,LEN(@var2)-2)) = SUBSTRING(@var2,2,LEN(@var2)-2)) OR field2 = @var2)
AND (@var3 = '(ALL)' OR (@var3 like '(%)' AND IsNull(field3,SUBSTRING(@var3,2,LEN(@var3)-2)) = SUBSTRING(@var3,2,LEN(@var3)-2)) OR field3 = @var3)

要得出我的第二个答案，我建议将完整的代码扩展到更多可读性：

：

declare @var1 varchar(100)
declare @var2 varchar(100)
declare @var3 varchar(100)
set @var1 = '(ALL)'
set @var2 = '(x)'
set @var3 = 'data'
declare @var1Inner varchar(100)
declare @var2Inner varchar(100)
declare @var3Inner varchar(100)
declare @var1withNull bit
declare @var2withNull bit
declare @var3withNull bit
SELECT @var1withNull = 0, @var2withNull = 0, @var3withNull = 0
IF (@var1 like '(%)') BEGIN
    SELECT @var1withNull = 1, @var1Inner = SUBSTRING(@var1,2,LEN(@var1)-2)
END
IF (@var2 like '(%)') BEGIN
    SELECT @var2withNull = 1, @var2Inner =  SUBSTRING(@var1,2,LEN(@var1)-2)
END
IF (@var3 like '(%)') BEGIN
    SELECT @var3withNull = 1, @var3Inner = SUBSTRING(@var3,2,LEN(@var3)-2)
END
SELECT *
FROM table 
WHERE (@var1 = '(ALL)' OR (@var1withNull = 1 AND IsNull(field1,@var1withNull) = @var1withNull) OR field1 = @var1)
AND (@var2 = '(ALL)' OR (@var2withNull = 1 AND IsNull(field2,@var2withNull) = @var2withNull) OR field2 = @var2)
AND (@var3 = '(ALL)' OR (@var3withNull = 1 AND IsNull(field3,@var3withNull) = @var3withNull) OR field3 = @var3)