db2 向where子句添加条件

von4xj4u 于 2022-11-07 发布在 DB2

关注(0)|答案(1)|浏览(340)

如何编写一个SQL语句，使其仅在参数有值的情况下在where子句中包含条件（我仅希望在参数有有效值的情况下在where子句中包含field2检查）

SELECT *  FROM abc
WHERE if(:param1 has a value that is not null or white space) then field1 = :param1   
AND   if(:param2 has a value that is not null or white space) then field2 = :param2   
AND   if(:param3 has a value that is not null or white space) then field3 = :param3   
AND   if(:param4 has a value that is not null or white space) then field4 = :param4  
AND   if(:param5 has a value that is not null or white space) then field5 = :param5

db2

来源：https://stackoverflow.com/questions/68440126/adding-conditions-to-where-clause

1条答案

按热度按时间

ibrsph3r1#

典型的方法会使用or，并针对参数进行明确的NULL比较：

SELECT *
FROM abc
WHERE (:param1 is null OR field1 = :param1) AND
      (:param2 is null OR field2 = :param2) AND
      (:param3 is null OR field3 = :param3) AND
      (:param4 is null OR field4 = :param4) AND
      (:param5 is null OR field5 = :param5) ;

如果你特别想白色的行为和NULL s一样，那么这就变得更复杂了。

WHERE (NULLIF(REPLACE(:param1, ' '), '') is null OR field1 = :param1) AND
      (NULLIF(REPLACE(:param2, ' '), '') is null OR field2 = :param2) AND
      (NULLIF(REPLACE(:param3, ' '), '') is null OR field3 = :param3) AND
      (NULLIF(REPLACE(:param4, ' '), '') is null OR field4 = :param4) AND
      (NULLIF(REPLACE(:param5, ' '), '') is null OR field5 = :param5) ;

但是，我认为在传入参数之前将空字符串转换为NULL会更常见，因此SQL只将NULL作为一个特殊值使用。

赞(0）回复(0）举报 2022-11-07

我来回答

db2 向where子句添加条件

1条答案

相关问题

热门标签

最新问答