sql - 如何在where条件SQL中插入if

Question

我有这个问题，

SELECT [Equipment]
      ,[CounterType]      
      ,SUM([Quantity])
FROM [FlexNet].[dbo].[AT_MACHINE_COUNTER]
WHERE 
    CreatedOn IS NOT NULL
    AND [FlexNet].[dbo].AF_GetUTCToLocal(CreatedOn) between @DATA_START and @DATA_END
    AND [Equipment] LIKE @MACHINE
    AND CounterType IN 
    (CASE WHEN @MACHINE LIKE 'PACKER001' THEN 2 END)

我想在 WHERE 条件中插入这个 IF:

IF @MACHINE LIKE 'PACKER001' THEN CounterType = 1
ELSE 'I don't want apply any condition'

所以查询应该与此类似(但我知道找不到此查询):

    SELECT [Equipment]
          ,[CounterType]      
          ,SUM([Quantity])
    FROM [FlexNet].[dbo].[AT_MACHINE_COUNTER]
    WHERE 
        CreatedOn IS NOT NULL
        AND [FlexNet].[dbo].AF_GetUTCToLocal(CreatedOn) between @DATA_START and @DATA_END
        AND [Equipment] LIKE @MACHINE
                    IF @MACHINE LIKE 'PACKER001' THEN 
                       AND CounterType =2
                    END IF

我们能帮我吗？常客

Answer 1

您在第一次尝试时就差不多成功了。只需在 Case Statement 中添加 Else 部分即可解决 CounterType 问题。

SELECT [Equipment],
       [CounterType],
       Sum([Quantity])
FROM   [FlexNet].[dbo].[AT_MACHINE_COUNTER]
WHERE  CreatedOn IS NOT NULL
       AND [FlexNet].[dbo].Af_getutctolocal(CreatedOn) BETWEEN @DATA_START AND @DATA_END
       AND [Equipment] LIKE @MACHINE
       AND CounterType = CASE
                           WHEN @MACHINE LIKE 'PACKER001' THEN 2
                           ELSE CounterType
                         END