主页 / dba / 问题 / 255002
Accepted
user196572
Asked: 2019-12-07 01:54:13 +0800 CST

Where 子句中的空值检查条件

  • 772
DECLARE @SubDesc1 nvarchar(100)   
SET @SubDesc1 = null

我想做这样的事情。

查询 1

SELECT * 
From Table1   
where Category = 'Category1'   
  AND [Description] ='ABC'   
  AND SubDescription1 IS NULL   
   OR SubDescription1 = @SubDesc1

查询 2

SELECT * 
FROM Table1   
WHERE Category = 'Category1'   
  AND [Description] ='ABC'  
  AND SubDescription1 = (Case WHEN @SubDesc1 IS NOT NULL 
                              Then @SubDesc1
                              ELSE SubDescription1 
                              END)

如果我的，他们都没有解决@SubDesc1 = 'def'

但是，它会让我记录在哪里SubDescription1 Is null和SubDescription1 = 'def'

我怎样才能做到这一点？

sql-server null

1 个回答

  1. Best Answer

    如果 NULL 值@SubDesc1表示“返回所有记录，不应用过滤”，则

    SELECT * 
FROM Table1   
WHERE Category = 'Category1'   
  AND [Description] ='ABC'   
  AND (    SubDescription1 = @SubDesc1
        OR @SubDesc1 IS NULL)

    如果 NULL 值的@SubDesc1意思是“只返回那些字段也为 NULL 的记录”，那么

    SELECT * 
FROM Table1   
WHERE Category = 'Category1'   
  AND [Description] ='ABC'   
  AND (    SubDescription1 = @SubDesc1 
        OR COALESCE(@SubDesc1, SubDescription1) IS NULL )
    • 0

相关问题