SQL Server 中的条件 WHERE 子句

2021-12-01 00:00:00 sql where-clause tsql sql-server-2008 sql-server

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

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

应该是这样的:

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?

我不想使用动态 SQL，所以有没有其他方法，或者我是否必须使用一种解决方法，例如使用 if else 并使用具有不同 where<的相同查询/code> 子句?

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?

推荐答案

试试这个 

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)
    )

您可以在此处阅读有关条件 WHERE 的更多信息.

相关文章