sql查询语句中是否可以引入参数,做逻辑判断

select  

${IF(frequency="1","AA",IF(frequency="2","BB",IF(frequency="3","CC","")))} AS part_dt,

sum(business_volume) as Sales,

sum(valid_order) as TC

from 

(

select 

part_dt,

store_id,

business_volume ,

valid_order

from ads_platform_store_day   

where 1=1 

${IF(frequency="1","AND part_dt >= '"+format(dateinweek(endDate,1),'yyyyMMdd')+"' AND part_dt<= '"+format(endDate,'yyyyMMdd')+"'" ,

IF(frequency="2","AND part_dt >= '"+format(dateinmonth(endDate,1),'yyyyMMdd')+"' AND part_dt<= '"+format(endDate,'yyyyMMdd')+"'",

IF(frequency="3","AND part_dt >= '"+format(dateinyear(endDate,1),'yyyyMMdd')+"' AND part_dt<= '"+format(endDate,'yyyyMMdd')+"'"

,"")))}

and store_id in ( '${store_id}')

)a 

如上SQL,运行后报错:

image.png

image.png

请教一下,是否可以用参数做逻辑判断,如果不能,该怎么解决

FineReport 用户N0188713 发布于 2022-5-7 10:51
1min目标场景问卷 立即参与
回答问题
悬赏:3 F币 + 添加悬赏
提示:增加悬赏、完善问题、追问等操作,可使您的问题被置顶,并向所有关注者发送通知
共3回答
最佳回答
0
Z4u3z1Lv6专家互助
发布于2022-5-7 10:53(编辑于 2022-5-7 10:55)

上边三个 在下边的子查询中根本没得

image.png

改成

select  

${IF(frequency="1","'AA'",IF(frequency="2","'BB'",IF(frequency="3","'CC'","")))} AS part_dt,

sum(business_volume) as Sales,

sum(valid_order) as TC

from 

(

select 

part_dt,

store_id,

business_volume ,

valid_order

from ads_platform_store_day   

where 1=1 

${IF(frequency="1","AND part_dt >= '"+format(dateinweek(endDate,1),'yyyyMMdd')+"' AND part_dt<= '"+format(endDate,'yyyyMMdd')+"'" ,

IF(frequency="2","AND part_dt >= '"+format(dateinmonth(endDate,1),'yyyyMMdd')+"' AND part_dt<= '"+format(endDate,'yyyyMMdd')+"'",

IF(frequency="3","AND part_dt >= '"+format(dateinyear(endDate,1),'yyyyMMdd')+"' AND part_dt<= '"+format(endDate,'yyyyMMdd')+"'"

,"")))}

and store_id in ( '${store_id}')

)a 

  • 用户N0188713 用户N0188713(提问者) 谢谢,是这样。你说的“上边三个 在下边的子查询中根本没得”没有理解什么意思。我这边是引入了参数判断,如果frequency=\"1\" 是取日数据,等于2是取周数据,等于3是月数据,SQL是一个汇总值,如果1的话就是最近7天汇总,如果等于2就是月初到当日数据汇总,如果等于3 就是当年年初到当日数据
    2022-05-07 11:11 
  • Z4u3z1 Z4u3z1 回复 用户N0188713(提问者) 按照你原来的SQL最终执行的语句是:frequency=\"1\" 时:SELECT AA AS part_dt, sum(business_volume) as Sales, sum(valid_order) as TC from 子查询 这个语句肯定报错,因为from 里面的子查询(可以想象为视图)它只有part_dt,store_id,business_volume ,valid_order 这四个字段
    2022-05-07 11:18 
  • 用户N0188713 用户N0188713(提问者) 回复 Z4u3z1 明白了,多谢
    2022-05-07 11:19 
最佳回答
0
15922204585Lv6高级互助
发布于2022-5-7 10:52

case when then end

最佳回答
0
重庆一棵草Lv4中级互助
发布于2022-5-7 10:52(编辑于 2022-5-7 10:55)

可以的。就看你想引入什么。根据参数查询不同的字段吗?image.png你这里是想展示不同的字段吗?你可以${IF(frequency="1","AA",“”)} AS part_dt,

${IF(frequency="2","BB",“”)} AS part_dt,

${IF(frequency="3","CC",“”)} AS part_dt,后面在where里面也可以这样去进行判断取值。

  • 3关注人数
  • 345浏览人数
  • 最后回答于:2022-5-7 10:55
    请选择关闭问题的原因
    确定 取消
    返回顶部