WHERE子句未过滤全部条件：SQL查询部分筛选规则失效求助

嘿，我一眼就看出问题出在哪了——你的WHERE子句里的AND和OR优先级混淆，这直接导致逻辑分组错误，部分筛选条件根本没按你预期的生效！

问题根源 #

在SQL语法里，AND的优先级比OR高，所以你的原查询会被数据库自动解析成这样的逻辑结构：

WHERE (cl.IdClient = 3 AND f.NameLawyer = 'FooName' AND f.IdTicket is null 
      AND month(f.date_payed) = month(...) AND YEAR(f.date_payed) = year(...))
OR 
(month(f.date_payed) IS NULL 
 AND f.Date_Bill <= ... AND c.Date_Payment_received BETWEEN ...)

看到没？OR把整个WHERE子句拆成了两个独立的逻辑块：第一个块包含了你指定的客户端、律师、IdTicket等核心筛选条件，第二个块却完全绕过了这些条件——只要满足日期相关规则就会被返回，这就是你觉得部分筛选条件被忽略的核心原因！

修正方案 #

用括号明确逻辑分组，确保所有强制筛选条件（Client、NameLawyer、IdTicket）都作用于所有分支。把日期相关的两个分支（有支付日期的情况和无支付日期的情况）用括号括起来，让它们和前面的强制条件用AND绑定：

SELECT DISTINCT 
    f.NameLawyer AS 'Comisiona', 
    f.Bill As 'Factura', 
    f.Amount AS 'Monto_Comisionado', 
    f.IdTicket 
FROM Bills f 
INNER JOIN Cases i on f.CaseName = i.CaseName 
INNER JOIN Payments_received c ON f.IdPRC = c.IdPRC 
INNER JOIN Clients cl ON f.IdClient = cl.IdClient 
LEFT JOIN type_of_reclaim tr ON tr.id_claim = i.type_of_claim 
WHERE 
    -- 强制筛选条件：所有结果必须满足这些规则
    cl.IdClient = 3 
    AND f.NameLawyer = 'FooName' 
    AND f.IdTicket is null 
    -- 日期逻辑分支：二选一，但都要满足上面的强制条件
    AND (
        (month(f.date_payed) = month(CAST('2020-09-14' AS DATETIME)) 
         AND YEAR(f.date_payed) = year(CAST('2020-09-14' AS DATETIME)))
        OR 
        (month(f.date_payed) IS NULL)
    )
    AND f.Date_Bill <= DATEFROMPARTS(
        YEAR(DATEADD(MM, -2, CAST('2020-09-14' AS DATE))), 
        MONTH(DATEADD(MM, -2, CAST('2020-09-14' AS DATE))), 
        DAY(EOMONTH((DATEADD(MM, -2, CAST('2020-09-14' AS DATE)))))
    ) 
    AND c.Date_Payment_received BETWEEN 
        DATEFROMPARTS(
            YEAR(DATEADD(MM, -2, CAST('2020-09-14' AS DATE))), 
            MONTH(DATEADD(MM, -2, CAST('2020-09-14' AS DATE))), 
            DAY(DATEADD(DAY, 1, EOMONTH(DATEADD(MM, -3, CAST('2020-09-14' AS DATE)))))
        ) 
        AND 
        DATEFROMPARTS(
            YEAR(CAST('2020-09-14' AS DATE)), 
            MONTH(CAST('2020-09-14' AS DATE)), 
            DAY(DATEADD(DAY, 10, EOMONTH(CAST('2020-09-14' AS DATE))))
        )

额外优化建议 #

为了让代码更易读、减少重复计算，你可以把固定日期2020-09-14先赋值给变量（以SQL Server为例）：

DECLARE @TargetDate DATE = '2020-09-14';
-- 后续所有日期计算都用@TargetDate代替重复的CAST操作

这样不仅降低了出错概率，后续修改目标日期时也只需要改一处，维护起来更省心。

内容的提问来源于stack exchange，提问作者Nacho Lazbal