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

我想通过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，这种方法没法用，不符合代码规范；

https://blog.csdn.net/sinat_16381803/article/details/51144746

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

根据你上面的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>