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