sql-server-2005 - 试图理解 SQL where 子句

Question

我希望有人可以帮助解释此 SQL 子句的最后一行代码 (@Active=1....):

SELECT DISTINCT LOC_ID
    ,LOC_CODE
    ,ADDR_LINE_1
    ,ADDR_LINE_2
    ,ADDR_LINE_3
    ,CITY
    ,STATE
    ,COUNTRY
    ,POSTAL_CODE
    ,COMPANY
    ,OPERATION_TYPE
    ,PROCESS
        ,ADDR_LINE_1 + ',' +ADDR_LINE_2+ ',' + CITY + '-' + COUNTRY + ' ' + POSTAL_CODE AS ADDRESS
        ,COMPANY + '.' + CENTER + '.' + OPERATION_TYPE + '.' + PROCESS AS Proc_Combo
    ,CASE   WHEN INACTIVE_DATE IS NULL THEN 'Active'
        WHEN INACTIVE_DATE IS NOT NULL THEN 'InActive'
    ELSE 'UNKNOWN' END AS INACTIVE_DATE
    ,CASE   WHEN INSTANCE_ID = 1 THEN 'AP' 
        WHEN INSTANCE_ID = 2 THEN 'GAP'     
    ELSE 'ALL' END AS INSTANCE_ID
FROM HR_Locations
WHERE
    (@STATE IS NOT NULL AND STATE =@STATE OR @STATE='' AND STATE =STATE OR @STATE IS NULL AND 1=1) 
AND
    (@ACTIVE=1 AND INACTIVE_DATE IS NULL OR @ACTIVE=2 AND INACTIVE_DATE IS NOT NULL OR @ACTIVE=0 AND 1=1 OR @ACTIVE IS NULL)  

我遇到过这样的代码，只是它不包含在“IF” block 中

Answer 1

重写为更容易理解

(@ACTIVE=1 AND INACTIVE_DATE IS NULL)
OR (@ACTIVE=2 AND INACTIVE_DATE IS NOT NULL)
OR (@ACTIVE=0 AND 1=1)
OR @ACTIVE IS NULL

我觉得有趣的部分是(@ACTIVE=0 AND 1=1)。这将 AND 条件 @ACTIVE=0 和值 TRUE。在我看来，这可能是一个错字。我见过像 (@ACTIVE=0 OR 1=1) 这样的东西使条件有点“可选”，但在这种情况下它看起来是不必要的。您能否尝试在不使用 AND 1=1 的情况下运行它们的查询，看看结果是否相同？