SQL Server中的条件WHERE子句

I am creating a SQL query in which I need a conditional where clause.

我正在创建一个SQL查询，其中需要一个条件where子句。

It should be something like this:

应该是这样的:

SELECT 
    DateAppr,
    TimeAppr,
    TAT,
    LaserLTR,
    Permit,
    LtrPrinter,
    JobName,
    JobNumber,
    JobDesc,
    ActQty,
    (ActQty-LtrPrinted) AS L,
    (ActQty-QtyInserted) AS M,
    ((ActQty-LtrPrinted)-(ActQty-QtyInserted)) AS N
FROM 
    [test].[dbo].[MM]
WHERE 
    DateDropped = 0
            --This is where i need the conditional clause 
    AND CASE
            WHEN @JobsOnHold = 1 THEN DateAppr >=  0
            ELSE  DateAppr != 0
        END

The above query is not working. Is this not the correct syntax or is there another way to do this that I don't know?

上面的查询不起作用。这不是正确的语法，还是有其他我不知道的方法?

I don't want to use dynamic SQL, so is there any other way or do I have to use a workaround like using if else and using the same query with different where clauses?

我不想使用动态SQL，那么还有其他的方法吗，或者我必须使用类似if else和使用同一个查询并使用不同where子句的方法吗?

4 个解决方案

#1

Try this

试试这个

SELECT 
    DateAppr,
    TimeAppr,
    TAT,
    LaserLTR,
    Permit,
    LtrPrinter,
    JobName,
    JobNumber,
    JobDesc,
    ActQty,
    (ActQty-LtrPrinted) AS L,
    (ActQty-QtyInserted) AS M,
    ((ActQty-LtrPrinted)-(ActQty-QtyInserted)) AS N
FROM 
    [test].[dbo].[MM]
WHERE 
    DateDropped = 0
    AND (
    (ISNULL(@JobsOnHold, 0) = 1 AND DateAppr >= 0) 
    OR 
    (ISNULL(@JobsOnHold, 0) != 1 AND DateAppr != 0)
    )

你可以在这里阅读更多关于条件句的内容。

#2

Try this one -

试试这个,

WHERE DateDropped = 0
    AND (
        (ISNULL(@JobsOnHold, 0) = 1 AND DateAppr >= 0) 
        OR 
        (ISNULL(@JobsOnHold, 0) != 1 AND DateAppr != 0)
    )

#3

To answer the underlying question of how to use a CASE expression in the WHERE clause:

回答如何在WHERE子句中使用CASE表达式的基本问题:

First remember that the value of a CASE expression has to have a normal data type value, not a boolean value. It has to be a varchar, or an int, or something. It's the same reason you can't say SELECT Name, 76 = Age FROM [...] and expect to get 'Frank', FALSE in the result set.

首先要记住，CASE表达式的值必须有一个普通的数据类型值，而不是布尔值。它必须是varchar，或int，或其他什么。这也是为什么你不能说SELECT Name, 76 = Age FROM…并期望在结果集中得到'Frank'， FALSE。

Additionally, all expressions in a WHERE clause need to have a boolean value. They can't have a value of a varchar or an int. You can't say WHERE Name; or WHERE 'Frank';. You have to use a comparison operator to make it a boolean expression, so WHERE Name = 'Frank';

此外，WHERE子句中的所有表达式都需要具有布尔值。它们不能有varchar或int的值，也不能说名字在哪里;或“弗兰克”;。你必须使用一个比较运算符使它成为一个布尔表达式，因此，Name = 'Frank';

That means that the CASE expression must be on one side of a boolean expression. You have to compare the CASE expression to something. It can't stand by itself!

这意味着CASE表达式必须位于布尔表达式的一边。你必须将情况表达式与某物进行比较。它不能自己站着!

Here:

在这里:

WHERE 
    DateDropped = 0
    AND CASE
            WHEN @JobsOnHold  = 1 AND DateAppr >= 0 THEN 'True'
            WHEN DateAppr != 0 THEN 'True'
            ELSE 'False'
        END = 'True'

Notice how in the end the CASE expression on the left will turn the boolean expression into either 'True' = 'True' or 'False' = 'True'.

请注意，左边的CASE表达式如何将布尔表达式转换为'True' = 'True'或'False' = 'True'。

Note that there's nothing special about 'False' and 'True'. You can use 0 and 1 if you'd rather, too.

注意，“假”和“真”并没有什么特别之处。你也可以用0和1。

You can typically rewrite the CASE expression into boolean expressions we're more familiar with, and that's generally better for performance. However, sometimes is easier or more maintainable to use an existing expression than it is to convert the logic.

通常可以将CASE表达式重写为我们更熟悉的布尔表达式，这通常对性能更好。然而，有时使用现有表达式比转换逻辑更容易或更易于维护。

#4

The problem with your query is that in CASE expressions, the THEN and ELSE parts have to have an expression that evaluates to a number or a varchar or any other datatype but not to a boolean value.

查询的问题是，在CASE表达式中，THEN和ELSE部分必须有一个表达式，该表达式的计算结果是一个数字、一个varchar或任何其他数据类型，而不是一个布尔值。

You just need to use boolean logic (or rather the ternary logic that SQL uses) and rewrite it:

您只需使用布尔逻辑(或者SQL使用的三元逻辑)并重写它:

WHERE 
    DateDropped = 0
AND ( @JobsOnHold = 1 AND DateAppr >= 0 
   OR (@JobsOnHold <> 1 OR @JobsOnHold IS NULL) AND DateAppr <> 0
    )

#1