|
每次遇到参数为空选择全部的写法，总会因为单引号，双引号这些乱七八糟的格式搞的头疼，而且有时候玩的比较花，例如if里面套if，这时候里面的参数为空选择全部的模式，引号这些更加麻烦，我稍微整理了几个我最近用的，一般有经验的应该可以看懂吧（模糊查询、下拉树的treelayer这种，欢迎大佬们能帮我把这种写法补充到4和5里面，这个我不会哈哈哈哈）
1、
a)下拉复选框设置分隔符(控件里面设置分隔符【','】)，参数为空选择全部
b)下拉框等于的写法
c)文本控件模糊查询
d)下拉复选框多选模糊查询的写法【复选框返回值类型选择分隔符默认为{,} 即可，不需要设置成{','}】（判断如果th_reason字段为null或者空，赋值为others）
SELECT * FROM table t1
where 1=1
${if(len(areaid)=0,"","and t1.area_id in ('"+areaid+"')")}
${if(len(name)=0,"","and t1.name = '"+name+"'")}
${if(len(companyid) == 0,"","and company_id like '%"+companyid+"%'")}
${if(len(threason) == 0,""," AND ( (case when len(t1.th_reason)=0 then 'others' else isnull(a.th_reason,'others') end) like'%"+JOINARRAY(SPLIT(threason,','),"' OR (case when len(t1.th_reason)=0 then 'others' else isnull(t1.th_reason,'others') end) like '%")+"%') ")}
2、下拉树控件参数为空选择全部（控件设置中4个选项剔除：结果返回完整层次路径，勾选其他3个）
SELECT * FROM table t1
where 1=1
${if(len(areaid)=0,"","and t1.area_id in ('"+replace(areaid,",","','")+"')")}
3、下拉树控件参数为空选择全部（控件设置中4个选项全都勾选）
SELECT * FROM table t1
where 1=1
${if(len(areaid)==0,""," and a.area_id in ("+"'"+treelayer(areaid,true,"\',\'")+"'"+")")}
4、数据来源于同一个表，根据参数面板控件选项控制(不同选项的控件名称为wd)，选择不同的参数执行不同的运算条件
SELECT * FROM table t1
where 1=1
${if(wd='1',"and t1.area_id in ('"+replace(areaid,",","','")+"')",
if(wd='2', "and t1.team_id in ('"+replace(teamid,",","','")+"')" ,
if(wd='3', "and t1.org_id in ('"+replace(orgid,",","','")+"')" ," and 1=2")
))}
5、数据来源于不同的表，按照筛选条件运行不同的sql语句；参数面板的条件控件为type（d，w）,然后根据不同的wd控件参数运行不同的筛选条件(if里面套if)
${
if(type='d',
"
select * from table1 a where 1=1 and FillDate = '"+ repyear +"'
"+if(len(areaid)=0,"","and area_id in ('"+replace(areaid,",","','")+"')")+"
"+if(wd='2',if(len(teamid)=0,"","and team_id in ('"+replace(teamid,",","','")+"')"),
if(wd='3',if(len(companyid)=0,"","and company_id in ('"+replace(companyid,",","','")+"')"),
if(wd='4',if(len(orgid)=0,"","and org_id in ('"+replace(orgid,",","','")+"')"),"")))+"
",
if(type='w',
"
select * from table2 a where 1=1
and week >=( select min(week) from table3 where left(a.FillDate,7) = '"+left(repyear,7)+"' )
and week <=( select max(week) from table4 where left(a.FillDate,7) = '"+left(repyear,7)+"' )
"+if(len(areaid)=0,"","and areaid in ('"+replace(areaid,",","','")+"')")+"
"+if(wd='2',if(len(teamid)=0,"","and teamid in ('"+replace(teamid,",","','")+"')"),
if(wd='3',if(len(companyid)=0,"","and companyid in ('"+replace(companyid,",","','")+"')"),
if(wd='4',if(len(orgid)=0,"","and orgid in ('"+replace(orgid,",","','")+"')"),"")))+"
",
"select * from table a where 1=2"
)
)} | 
