sql - 协助多个 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
我似乎在进行测试,以混合数据,彼此不匹配。非常感谢您的帮助,谢谢
解决方案
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')
)
推荐阅读
- python-3.x - AsyncSSH 创建 SFTP 客户端错误 - 太多值无法解压
- java - Sparql 查询获取超时异常
- excel - 在 excel 单元格的渲染/显示值上使用公式
- android - 如何使用改造和.net核心从android上传几张图片?
- angular - 模板解析错误:无法绑定到“任务”,因为它不是“任务列表”的已知属性
- shell - 什么shell命令将单词分成变量?
- python - Plotly中同一X轴和Y轴上的多个条形图
- grails - 元类在测试期间不替换实例方法?
- php - 在 PHP 类的静态变量中传递函数
- mysql - 无法在 MacOS 上运行 MySQL