SQL语句该怎么写？

一个简单查询，已有6个参数，SQL脚本如下：

SELECT * FROM Table1 WITH (NOLOCK)

WHERE 1=1

${if(len(p_depot)=0,"","AND depot_code LIKE '"+p_depot+"%'")}

${if(len(p_region)=0,"","AND depot_region in ('"+p_region+"')")}

${if(len(p_province)=0,"","AND depot_province in ('"+p_province+"')")}

${if(len(p_city)=0,"","AND depot_city in ('"+p_city+"')")}

${if(len(p_deptyp_c)=0,"","AND depot_type in ('"+p_deptyp_c+"')")}

${if(len(p_Store)=0,"","AND depot_name in ('"+p_Store+"')")}

想增加一个复选框chkName，当这个复选框为选中的时候，执行查询条件

AND depot_name='AAA'，同时忽略其他6个参数的条件。如这个复选框没有被选中，继续保持上面6个参数的查询逻辑。这个语句该怎么写？

FineReport kenlewis 发布于 2024-6-20 15:05

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：3 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共4回答

最佳回答

华莉星宸Lv6高级互助
发布于2024-6-20 15:13

SELECT * FROM Table1 WITH (NOLOCK)

WHERE 1=1

${if(len(chkName)<>0,"","/*")}

AND depot_name='AAA'

${if(len(chkName)<>0,"","*/")}

${if(len(chkName)=0,"","/*")}

${if(len(p_depot)=0,"","AND depot_code LIKE '"+p_depot+"%'")}

${if(len(p_region)=0,"","AND depot_region in ('"+p_region+"')")}

${if(len(p_province)=0,"","AND depot_province in ('"+p_province+"')")}

${if(len(p_city)=0,"","AND depot_city in ('"+p_city+"')")}

${if(len(p_deptyp_c)=0,"","AND depot_type in ('"+p_deptyp_c+"')")}

${if(len(p_Store)=0,"","AND depot_name in ('"+p_Store+"')")}

${if(len(chkName)=0,"","*/")}

举报收起评论(2)

kenlewis(提问者) 这方法最简单

2024-06-20 15:16

华莉星宸回复 kenlewis(提问者) 是的根据条件注释语句

2024-06-20 15:17

ID1208Lv6高级互助
发布于2024-6-20 15:12

where 后面加case when 试试

case when-https://blog.csdn.net/Min_Monk/article/details/103492654

RJ6688Lv3见习互助
发布于2024-6-20 15:12（编辑于 2024-6-20 15:14）

SELECT * FROM Table1 WITH (NOLOCK)

WHERE 1=1

${if(len(chkName)=0,"","AND depot_name in ('"+chkName+"') ")}

${if(OR(len(p_depot)=0,len(chkName)>0),"","AND depot_code LIKE '"+p_depot+"%'")}

${if(OR(len(p_region)=0,len(chkName)>0),"","AND depot_region in ('"+p_region+"')")}

${if(OR(len(p_province)=0,len(chkName)>0),"","AND depot_province in ('"+p_province+"')")}

${if(OR(len(p_city)=0,len(chkName)>0),"","AND depot_city in ('"+p_city+"')")}

${if(OR(len(p_deptyp_c)=0,len(chkName)>0),"","AND depot_type in ('"+p_deptyp_c+"')")}

${if(OR(len(p_Store)=0,len(chkName)>0),"","AND depot_name in ('"+p_Store+"')")}

举报收起评论(1)

kenlewis(提问者) 您这个方法也很好，但是稍显麻烦一些。谢谢您的答案，对我也是有帮助的。

2024-06-20 15:16

很困想睡觉Lv3初级互助
发布于2024-6-20 15:17

SELECT * FROM Table1 WITH (NOLOCK)

WHERE 1=1

${if(len(chkName)=0,"","AND depot_name='AAA'")}

${if(len(p_depot)=0,"","AND depot_code LIKE '"+p_depot+"%'")}

${if(len(p_region)=0,"","AND depot_region in ('"+p_region+"')")}

${if(len(p_province)=0,"","AND depot_province in ('"+p_province+"')")}

${if(len(p_city)=0,"","AND depot_city in ('"+p_city+"')")}

${if(len(p_deptyp_c)=0,"","AND depot_type in ('"+p_deptyp_c+"')")}

${if(len(p_Store)=0,"","AND depot_name in ('"+p_Store+"')")}

试试再加一个JS隐藏控件，cnkName控件是使用隐藏其他控件，保证其他控件满足第一个条件

https://help.fanruan.com/finereport/doc-view-1195.html?source=4#

举报收起评论(2)

kenlewis(提问者) 但这样做有一个风险，就是控件虽然被隐藏了，但是它的值是不是仍然可以读出来呢？如果在JS脚本里将控件值清空掉的话，又不符合我的题目要求了。

2024-06-20 15:26

很困想睡觉回复 kenlewis(提问者) JS设置为不可用啊，有值不可用一样的效果。不用纠结这个了，这个思路太麻烦，1楼那个注释的思路目前来看是最优解。没想到注释还能这么用。

2024-06-20 15:32

6关注人数

175浏览人数

最后回答于：2024-6-20 15:17

活动推荐更多

热门课程更多

请选择关闭问题的原因

确定取消

提问

悬赏者（1）

撤回悬赏（1）

添加悬赏

请选择关闭问题的原因