首页 > 解决方案 > 协助多个 WHERE 子句,以允许用户搜索其中一个,而不会在途中互相跺脚

问题描述

我有下面的简单代码

SELECT
      c.account,
      c.apar_id,
      c.amount,
      c.client,
      c.dim_2,
      c.dim_7,
      c.due_date,
      c.ext_inv_ref,
      c.period,
      c.voucher_date,
      c.voucher_no,
      c.voucher_type,
      c.sequence_no,
      c.rest_amount
      FROM acutrans c
      LEFT OUTER JOIN aglrelvalue r6
      ON r6.client = 'WEBE' AND r6.attribute_id = 'A4' AND r6.rel_attr_id = 'ZG06' AND c.apar_id = r6.att_value

      WHERE 
      c.client = 'WEBE' 
      AND c.period <='202007'
      AND (c.apar_id IS NULL OR c.apar_id ='CL25791') 
      OR (r6.rel_value IS NULL OR r6.rel_value ='*')
      AND (c.dim_7 IS NULL OR c.dim_7 >='') 
      AND (c.dim_7 IS NULL OR c.dim_7 <='BR36')

用户希望能够通过以下任一方式搜索数据: c.apar_id r6.rel_value c.dim_7

我似乎在进行测试,以混合数据,彼此不匹配。非常感谢您的帮助,谢谢

标签: sqlwhere-clause

解决方案


AND混合和时需要括号(通常)OR。我只能推测你真正想要什么,但类似:

WHERE c.client = 'WEBE' AND
      c.period <= '202007' AND
      (c.apar_id IS NULL OR c.apar_id = 'CL25791')  AND
      ((r6.rel_value IS NULL OR r6.rel_value = '*') AND
       (c.dim_7 IS NULL OR c.dim_7 >= '') AND
       (c.dim_7 IS NULL OR c.dim_7 <= 'BR36')
      )

推荐阅读