SQL查询@insurance参数过滤逻辑异常：值为0时结果与1+2+3总和不符

你的SQL过滤逻辑确实存在错误，核心问题出在@insurance参数的分支判断逻辑完全不符合预期设计。

核心错误点 #

你当前写的保险过滤逻辑是：

AND (@insurance = 0
    OR (@insurance = 1 AND pai.isprimary = 1 AND i.insuranceId IN (1, 2, 3, 4))
    OR (@insurance = 2 AND pai.isprimary = 1 AND i.insuranceId NOT IN (1, 2, 3, 4))
    OR (@insurance = 3 AND i.insuranceName IS NULL))

这段逻辑的实际执行规则是：只要@insurance=0，整个括号内的条件直接为真，不会做任何保险相关的过滤。所以除了@insurance=1/2/3覆盖的113条记录外，所有满足其他WHERE条件、但不符合1/2/3保险规则的记录也会被返回，这就是你多出来43条记录的原因。
你预期的@insurance=0是「包含1/2/3所有符合条件的记录」，但实际写的逻辑是「0的时候不校验任何保险规则」，两者完全不等价。

额外还有一个叠加因素：你对pai（患者保险表）用的是LEFT JOIN，当@insurance=1/2时要求pai.isprimary=1，相当于把LEFT JOIN转换成了近似INNER JOIN的效果，过滤掉了非主保险、无保险的冗余行；但@insurance=0时LEFT JOIN的效果完全保留，一个患者如果有多个非主保险记录，会关联生成多行数据，也会导致最终计数偏高。

修复方案 #

调整保险分支判断逻辑，让@insurance=0时也必须满足1/2/3对应的三个保险条件的并集：

AND (
    -- @insurance=1 规则
    (@insurance = 1 AND pai.isprimary = 1 AND i.insuranceId IN (1,2,3,4))
    -- @insurance=2 规则
    OR (@insurance = 2 AND pai.isprimary = 1 AND i.insuranceId NOT IN (1,2,3,4))
    -- @insurance=3 规则
    OR (@insurance = 3 AND i.insuranceName IS NULL)
    -- @insurance=0 规则：覆盖上述所有规则
    OR (@insurance = 0 AND (
        (pai.isprimary = 1 AND i.insuranceId IN (1,2,3,4))
        OR (pai.isprimary = 1 AND i.insuranceId NOT IN (1,2,3,4))
        OR (i.insuranceName IS NULL)
    ))
)

额外注意事项 #

1. 如果你发现修复后还是存在少量计数偏差，大概率是LEFT JOIN关联患者保险表时，一个患者对应多条保险记录产生了重复行，可以根据业务主键（比如patientId+productId）加DISTINCT去重，或者调整JOIN逻辑只关联主保险记录。
2. 代码中大量使用NOLOCK提示可能读到未提交的脏数据，非必要场景不建议使用。

内容的提问来源于stack exchange，提问作者coder rock