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}')

如上SQL，运行后报错：

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

FineReport 用户N0188713 发布于 2022-5-7 10:51

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：3 F币 + 添加悬赏

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

共3回答

最佳回答

Z4u3z1Lv6专家互助
发布于2022-5-7 10:53（编辑于 2022-5-7 10:55）

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

改成

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}')

举报收起评论(3)

用户N0188713(提问者) 谢谢，是这样。你说的“上边三个在下边的子查询中根本没得”没有理解什么意思。我这边是引入了参数判断，如果frequency=\"1\" 是取日数据，等于2是取周数据，等于3是月数据，SQL是一个汇总值，如果1的话就是最近7天汇总，如果等于2就是月初到当日数据汇总，如果等于3 就是当年年初到当日数据

2022-05-07 11:11

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