请问1301是什么问题造成的

SELECT distinct d.fappraiseeid,d.fappraisee,d.fstate,d.fyear,d.forder,d.flevel
FROM tb_scoredetail d
where case (select flevel from tb_user where fusername='${fr_username}')
when 1 then d.flevel=3 and d.fyear=${year}
${if(len(appraisee) == 0,"","and d.fappraiseeid = '" + appraisee + "'")}
when 2 then d.flevel=3 and d.fyear=${year}
${if(len(appraisee) == 0,"and d.fappraiseeid = ''","and d.fappraiseeid = '" + appraisee + "'")}

end
order by d.forder asc

FineReport 士大夫 发布于 2018-12-21 11:17 （编辑于 2018-12-21 11:29）

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：4 F币 + 添加悬赏

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

共2回答

最佳回答

axingLv6专家互助
发布于2018-12-21 11:47

sql改成这样

SELECT distinct d.fappraiseeid,d.fappraisee,d.fstate,d.fyear,d.forder,d.flevel
FROM tb_scoredetail d
where d.flevel=3 and d.fyear=${year}
  ${if(name==1&&len(appraisee)>0,"and d.fappraiseeid = '" + appraisee + "'","")}
  ${if(name==2&&len(appraisee)>0,"and d.fappraiseeid = '" + appraisee + "'","and d.fappraiseeid = ''")}
order by d.forder asc

然后定义一个模板参数，名字name，用sql（）函数获取select flevel from tb_user where fusername='${fr_username}'的值

touyuan001Lv6中级互助
发布于2018-12-21 12:03（编辑于 2018-12-21 12:03）

SELECT distinct d.fappraiseeid,d.fappraisee,d.fstate,d.fyear,d.forder,d.flevel

FROM tb_scoredetail d

left join tb_user u

on d.flevel=u.flevel and
where case
when (select flevel from tb_user where fusername='${fr_username}') = 1 then d.flevel=3 and d.fyear=${year}
${if(len(appraisee) == 0,"","and d.fappraiseeid = '" + appraisee + "'")}
when (select flevel from tb_user where fusername='${fr_username}') = 2 then d.flevel=3 and d.fyear=${year}
${if(len(appraisee) == 0,"and d.fappraiseeid = ''","and d.fappraiseeid = '" + appraisee + "'")}

end
order by d.forder asc

Case when 是连续的，并不是像 switch 一样

如果是 oracle 的话case when 可以换成用 decode（）