sql - 如何从这段丑陋的代码中重写 "where, and, or"语句

Question

我的代码可以工作，但是很糟糕。有没有办法以正确的方式清理这段代码？

此代码(存储过程)用于搜索一个表中不同列中的单词以及具有不同卷 ID (0-4) 的帖子

@searchval nvarchar(500),
@arrStatusTyp int,
@rolltypAdmin int,
@roll1 int,
@roll2 int,
@roll3 int,
@roll4 int,
@visningsperiod nvarchar(4)

SELECT  kk_aj_tbl_Arrangemang.ArrID    
    FROM    kk_aj_tbl_content INNER JOIN
                kk_aj_tbl_arridtoContent ON kk_aj_tbl_content.Contentid = kk_aj_tbl_arridtoContent.contentid INNER JOIN
                kk_aj_tbl_Arrangemang ON kk_aj_tbl_arridtoContent.arrid = kk_aj_tbl_Arrangemang.ArrID INNER JOIN
                kk_aj_tbl_ArrangemangStatus ON kk_aj_tbl_Arrangemang.ArrangemangStatusID = kk_aj_tbl_ArrangemangStatus.ArrangemangStatusID INNER JOIN
                kk_aj_tbl_Konstformtyp ON kk_aj_tbl_Arrangemang.KonstformID = kk_aj_tbl_Konstformtyp.KonstformID INNER JOIN
                Users ON kk_aj_tbl_Arrangemang.AdminuserID = Users.UserID INNER JOIN
                kk_aj_tbl_utovare ON kk_aj_tbl_Arrangemang.UtovarID = kk_aj_tbl_utovare.UtovarID
    WHERE  
        (kk_aj_tbl_Arrangemang.ArrangemangStatusID = @arrStatusTyp) AND (kk_aj_tbl_arridtoContent.Version=1) AND 
        (
            (kk_aj_tbl_content.Rubrik LIKE '%'+@searchval+'%') AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll1) OR
            (kk_aj_tbl_content.Rubrik LIKE '%'+@searchval+'%') AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll2) OR
            (kk_aj_tbl_content.Rubrik LIKE '%'+@searchval+'%') AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll3) OR
            (kk_aj_tbl_content.Rubrik LIKE '%'+@searchval+'%') AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll4) OR                 
            (kk_aj_tbl_content.Underrubrik LIKE '%'+@searchval+'%')AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll1) OR
            (kk_aj_tbl_content.Underrubrik LIKE '%'+@searchval+'%')AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll2) OR
            (kk_aj_tbl_content.Underrubrik LIKE '%'+@searchval+'%')AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll3) OR
            (kk_aj_tbl_content.Underrubrik LIKE '%'+@searchval+'%')AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll4) OR
            (kk_aj_tbl_utovare.Organisation LIKE '%'+@searchval+'%')AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll1) OR
            (kk_aj_tbl_utovare.Organisation LIKE '%'+@searchval+'%')AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll2) OR
            (kk_aj_tbl_utovare.Organisation LIKE '%'+@searchval+'%')AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll3) OR
            (kk_aj_tbl_utovare.Organisation LIKE '%'+@searchval+'%')AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll4) 
        )
    ORDER BY kk_aj_tbl_arridtoContent.datum DESC

感谢您的帮助

Answer 1

条件如下:

(cond1 OR cond2)
AND (cond1 OR cond3)
AND (cond1 OR cond4)
...

可以重写为:

cond1 AND (cond2 OR cond3 OR cond4)

因此请尝试以下 WHERE 子句:

...
WHERE  
kk_aj_tbl_Arrangemang.ArrangemangStatusID = @arrStatusTyp
AND kk_aj_tbl_arridtoContent.Version=1
AND kk_aj_tbl_Konstformtyp.KonstformID IN (@roll1, @roll2, @roll3, @roll4)           
AND ( 
    kk_aj_tbl_content.Rubrik LIKE '%'+@searchval+'%'
    OR kk_aj_tbl_content.Underrubrik LIKE '%'+@searchval+'%'
    OR kk_aj_tbl_content.Organisation LIKE '%'+@searchval+'%'
)