首页 文章

需要根据变量是否为空来从表中选择记录 . 如果为null，则检查不等于条件

提问于
浏览 次
0

这对于每天在sql上工作的人来说听起来很容易，这些人吃了很多时间 . 我有表，我需要根据变量值选择记录，如果值为null，则设置状态列不等于3，变量值不等于基于变量值的空过滤器 .

DECLARE @status int
 set @status  = 1

 SELECT change_set_history_id, files_changed, is_previewed 
    FROM dbo.ChangeSetHistory WITH (NOLOCK)
    WHERE [user_name] = 'djacob' AND 
          [culture] = 'nl-NL' AND
          (@status IS NULL AND [status] <> 3) OR (@status IS NOT NULL AND [status] =     @status) AND
          [approver] = null    AND    
          is_deleted = 0

如果它尝试使用这个部分查询它工作DECLARE @status int set @status = 1 select * from ChangeSetHistory where status = @status and user_name ='djacob'和culture ='nl-NL'

sql sql-server

2 回答

  • 0

    用另一对括号包裹 OR 条件：

    SELECT change_set_history_id, files_changed, is_previewed 
FROM dbo.ChangeSetHistory WITH (NOLOCK)
WHERE [user_name] = 'djacob' AND 
      [culture] = 'nl-NL' AND
      ((@status IS NULL AND [status] <> 3) OR (@status IS NOT NULL AND [status] =     @status)) AND
      [approver] IS null    AND    
      is_deleted = 0

    如果您的条件相当于：

    WHERE ([user_name] = 'djacob' AND [culture] = 'nl-NL' AND (@status IS NULL AND [status] <> 3)) OR 
      ((@status IS NOT NULL AND [status] =     @status) AND [approver] = null    AND  is_deleted = 0)

    测试用例：

    DECLARE @status int
SET @status = 1

    SELECT *
    FROM (
        SELECT NULL [status] UNION ALL
        SELECT 1 [status] UNION ALL
        SELECT 2 [status] UNION ALL
        SELECT 3 [status]
    ) T
    WHERE ((@status IS NULL AND [status] <> 3) OR (@status IS NOT NULL AND [status] = @status));

    我看到的其他问题是 [approver] = null 条件 . 它应该是 [approver] is null

    回复于
  • 1

    尽量不要在相同的上下文中混合使用 AND 和 OR .

    您错过了状态条件的全局括号：

    代替

    (@status IS NULL AND [status] <> 3) OR (@status IS NOT NULL AND [status] = @status)

    肯定是

    ((@status IS NULL AND [status] <> 3) OR (@status IS NOT NULL AND [status] = @status))

    它应该解决你的问题：

    DECLARE @status int
 set @status  = 1

 SELECT change_set_history_id, files_changed, is_previewed 
    FROM dbo.ChangeSetHistory WITH (NOLOCK)
    WHERE [user_name] = 'djacob' AND 
          [culture] = 'nl-NL' AND
          ((@status IS NULL AND [status] <> 3) OR (@status IS NOT NULL AND [status] =     @status)) AND
          [approver] = null    AND    
          is_deleted = 0
    回复于

相关问题