jeffery_12 2017-11-24 09:06 采纳率: 0%
浏览 7182

在mybatis中mapper.xml中如何实现sql动态条件查询结果后求和

现在遇到个状况,如下代码
select
rela.member_paper_id,rela.member_id,paper_type,rela.paper_id,rela.create_time,rela.update_time,rela.start_time,rela.end_time,me.member_name,pa.paper_name,concat (rela.paper_score ,'分' ) as paper_score,concat (pa.total_score ,'分' ) as total_score,
cast((
select count(1) from
training_member_paper_question_relation qrela
left join training_member_paper_relation mpr on qrela.member_paper_id = mpr.member_paper_id
where
qrela.member_paper_id = rela.member_paper_id and is_correct = '0'
) as char(5))as error_number,
cast((
select
SUM(d.question_score)
from
(select qrela.question_score from training_member_paper_question_relation qrela
left join training_paper_question que on qrela.question_id = que.question_id
where qrela.member_paper_id = '2017110900011225' and (que.question_type = 'single' or que.question_type = 'order' or que.question_type = 'multiple')
)as d
) as char(5)) as subjective_score,
cast((
select
COUNT(1)
from training_member_paper_question_relation qrela
left join training_paper_question que on qrela.question_id = que.question_id
where qrela.member_paper_id = rela.member_paper_id and (que.question_type = 'single' or que.question_type = 'order' or que.question_type = 'multiple')
) as char(5))as subjective_item
from
training_member_paper_relation rela
left join training_member me on rela.member_id = me.member_id
left join training_paper pa on rela.paper_id = pa.paper_id
WHERE rela.member_paper_id = '2017110900011225'
在这种情况下是可以查出member_paper_id = '2017110900011225'的数据,这个没问题,但我的WHERE条件后的rela.member_paper_id 在mapper是动态的(= #{memberPaperId,jdbcType=CHAR}) ,那么在上面 qrela.member_paper_id 就不可能为= '2017110900011225'不变的,但是我把它改成像下面一个字段的方式: qrela.member_paper_id = rela.member_paper_id 直接报错[Err] 1054 - Unknown column 'rela.member_paper_id' in 'where clause',
subjective_score字段的查询是先查出条件下的结果,再把结果求和,再把这个作为一个字段去查询,下面一个 subjective_item字段查询是直接查出数量,再作为一个字段查询,这个是没有问题的,请问大神这个有没有什么办法啊
**__

  • 写回答

1条回答 默认 最新

  • threenewbee 2017-11-24 15:05
    关注
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题