如何在 WHERE 子句中模拟合并条件

Question

>假设我有以下内容：

SELECT    * 
FROM      Xyz 
WHERE     (@a IS NULL OR a = @a) AND
          (@b IS NULL OR b = @b) AND
          (@c IS NULL OR c = @c)

我想完成的是更改 WHERE 子句以某种COALESCE方式使用这些条件，其中它将使用第一个不为 null 或用完条件的条件。

在伪语中，它将是这样的：

WHERE    IF @a IS NOT NULL THEN
         (
             a = @a
         )
         ELSE IF @b IS NOT NULL THEN 
         (
             b = @b
         )
         ELSE IF @c IS NOT NULL THEN 
         (
             c = @c
         )

这可能吗？

Answer 1

这会

将 NULL 视为通配符：

SELECT *
FROM MyTable
WHERE COALESCE(
  NULLIF(@a,a),
  NULLIF(@b,b),
  NULLIF(@c,c)
) IS NULL

这将使用第一个非空条件：

SELECT *
FROM MyTable
WHERE EXISTS (
  SELECT id,value
  FROM (VALUES (1,a),(2,b),(3,c)) t1(id,value)
  INTERSECT
  SELECT TOP 1 id,comparator
  FROM (VALUES (1,@a),(2,@b),(3,@c)) t(id,comparator)
  WHERE comparator IS NOT NULL
  ORDER BY id
)

Answer 2

可能有更优雅的方式，但这应该尊重所需的逻辑

where
(@a is not null and @a = a) or
(@a is null and @b is not null and @b = b) or
(@a is null and @b is null and @c is not null and @c = c)

可以（一点）简化为：

where
(@a = a) or
(@a is null and @b = b) or
(@a is null and @b is null and @c = c)

Answer 3

您不能将布尔表达式的结果用作 SQL Server 中的值，但您可以将其替换为逻辑1/0，如下所示：

WHERE
  CASE
              WHEN @a IS NOT NULL AND a = @a THEN 1
    ELSE CASE WHEN @b IS NOT NULL AND b = @b THEN 1
    ELSE CASE WHEN @c IS NOT NULL AND c = @c THEN 1
  END END END = 1

上面也可以这样重新排列（也许有些人会觉得这样逻辑更清晰）：

WHERE
  CASE
    WHEN @a IS NOT NULL AND a = @a THEN 1
    ELSE
      CASE
        WHEN @b IS NOT NULL AND b = @b THEN 1
        ELSE
          CASE
            WHEN @c IS NOT NULL AND c = @c THEN 1
          END
      END
  END = 1

Answer 4

/* 
Since you are dynamically changing your condition you will need to 
use Dynamic sql and and build your query dynamically something like
this......
*/
CREATE TABLE Test_Dynamic (ID INT, A INT, B INT, C INT)
INSERT INTO Test_Dynamic VALUES
(1, 100, 1000, 10000),
(2, 200, 2000, 20000),
(3, 300, 3000, 30000),
(4, 400, 4000, 40000)
DECLARE @a INT, @b INT, @c INT;
SET @a = 100;
--SET @b = 1000;
--SET @c = 10000;
DECLARE @Sql NVARCHAR(MAX);
SET @Sql = N'SELECT * FROM  Test_Dynamic  WHERE 1 = 1 '
IF (@a IS NOT NULL)
BEGIN
 SET @Sql = @Sql + N' AND A = @ar'
  IF (@b IS NOT NULL)
    BEGIN
     SET @Sql = @Sql + N' AND B = @br'
       IF (@c IS NOT NULL)
         BEGIN
         SET @Sql = @Sql + N' AND C = @cr'
         END   
    END
END
PRINT @Sql
EXECUTE sp_executesql @Sql
                      ,N'@ar INT, @br INT, @cr INT'
                      ,@ar = @a
                      ,@br = @b
                      ,@cr = @c