WHERE 子句中的条件

Question

我正在尝试根据表中一列的值更改 WHERE 子句。 我的表格是：

server_updates

k1ID        updID
----------- -----------

server_updates_cat

sucID        updID       catID       selectChildren   includeNullType
-----------  ----------- ----------- ---------------- --------------

server_updates_types

sucID       typeID
----------- -----------

这就是我正在做的事情：

DECLARE @k1_catID INT
DECLARE @k4_type INT

SET @k1_catID = 30
SET @k4_type = 1

SELECT suc.sucID, suc.updID FROM server_updates su
INNER JOIN server_updates_cat suc ON suc.updID = su.updID

WHERE
includeNullType = 0
    AND suc.catID = @k1_catID
    AND selectChildren = 1
    AND @k4_type IN (SELECT typeID FROM server_updates_types sut WHERE sut.sucID = suc.sucID)
OR
includeNullType = 1 
    AND KSUC.catID = @k1_catID
    AND selectChildren = 1
    AND (@k4_type IN (SELECT typeID FROM server_updates_types sut WHERE sut.sucID = suc.sucID) OR @k4_type IS NULL)

我也不想重复所有条件includeNullType = 0 或 includeNullType = 1，但更改我真正需要更改的唯一内容，即：

@k4_type IN (SELECT typeID FROM server_updates_types sut WHERE sut.sucID = suc.sucID)

还有另一种方法吗？

Answer 1

WHERE suc.catID = @k1_catID
AND selectChildren = 1
AND (
(includeNullType = 0   
AND @k4_type IN (SELECT typeID FROM server_updates_types sut WHERE sut.sucID = suc.sucID))
OR
(includeNullType = 1 
    AND (@k4_type IN (SELECT typeID FROM server_updates_types sut WHERE sut.sucID = suc.sucID) OR @k4_type IS NULL))
)

Answer 2

您可以重新排列 AND 和 OR。简化：

includeNull = 0 AND LONG_CONDITION
OR
includeNull = 1 AND (LONG_CONDITION OR COND2)

如果 includeNull 只能是 0 或 1，这在逻辑上等价于

LONG_CONDITION OR (includeNull = 1 AND COND2)