mybatis plus同时foreach同一个list的sql该怎么写 10C

我想通过SQL统计各个状态的数据,效果如下:
图片说明

我现在能写出查单条统计结果的sql

SELECT
    a.present_dept_id,
    sum(case when a.create_time is not null and a.add_dept_id='16bf41e8252c0a8c85ed00001' then 1 else 0 end) as createCount, 
    sum(case when a.state = 1 and a.add_dept_id='16bf41e8252c0a8c85ed00001' then 1 else 0 end) as notCreateCount, 
    sum(case when a.state = -1 and a.present_dept_id='16bf41e8252c0a8c85ed00001' then 1 else 0 end) as ignoreCount,
    sum(case when b.dispose_type = 2 and a.present_dept_id='16bf41e8252c0a8c85ed00001' then 1 else 0 end) as dealCount,
    sum(case when a.state = 3 and a.present_dept_id='16bf41e8252c0a8c85ed00001' then 1 else 0 end) as noNealCount,
    sum(case when a.state = 9 and a.present_dept_id='16bf41e8252c0a8c85ed00001' then 1 else 0 end) as deleteCount,
    sum(case when b.dispose_dept_id='16bf41e8252c0a8c85ed00001' and b.target_dept_id!='16bf41e8252c0a8c85ed00001' and b.dispose_type in(3,4,5) then 1 else 0 end) as turnOutCount   
  FROM
    tb_gongdan_detail a LEFT JOIN tb_gongdan_history b ON a.id=b.gongdan_id  

查出来的结果是这样的:
图片说明


现在,我有一个存放id的list,我想在SQL中的多个条件里foreach这个list,并且保证在每个条件中遍历的值都是同步进行的

List<String> idList

求教大佬这样的循环sql怎么写 ,跪谢大佬orz....

注:
①普通的foreach我会写,请不要无脑的丢一个foreach教程;
②请不要让我在java后台中每次传一个id循环调sql,这种方法没法用,不符合代码规范;

3个回答

根据你上面的sql add_dept_id 所有的ID用的是同一个值,你只需要在代码最外面判断下List idList SIZE大于 0。
然后直接foreach 将整个sql包起来

<foreach collection="idList" item="id" index="index">
SELECT
    a.present_dept_id,
    sum(case when a.create_time is not null and a.add_dept_id= #{id} then 1 else 0 end) as createCount, 
    sum(case when a.state = 1 and a.add_dept_id=#{id} then 1 else 0 end) as notCreateCount, 
    sum(case when a.state = -1 and a.present_dept_id=#{id} then 1 else 0 end) as ignoreCount,
    sum(case when b.dispose_type = 2 and a.present_dept_id=#{id} then 1 else 0 end) as dealCount,
    sum(case when a.state = 3 and a.present_dept_id=#{id} then 1 else 0 end) as noNealCount,
    sum(case when a.state = 9 and a.present_dept_id=#{id} then 1 else 0 end) as deleteCount,
    sum(case when b.dispose_dept_id=#{id} and b.target_dept_id!=#{id} and b.dispose_type in(3,4,5) then 1 else 0 end) as turnOutCount   
  FROM
    tb_gongdan_detail a LEFT JOIN tb_gongdan_history b ON a.id=b.gongdan_id  
</foreach>  

catfishlyf
catfishlyf 无脑刷分回答案例
12 个月之前 回复

没有太看懂你的表是什么情况 ,可以根据a.present_dept_id取group by 查询的话不用循环

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐