sql 统计语句 优化

sql 统计语句 优化

select sum(undoneCount)+sum(doneCount)+sum(rejectedCount) totalNum,max(undoneCount) undoneCount,max(doneCount) doneCount,max(rejectedCount) rejectedCount,process_name,process_id from (
select
sum(case when a.status in (0,1) then 1 else 0 end) undoneCount,
sum(case when a.status = 2 then 1 else 0 end) doneCount,
sum(case when a.status = 3 then 1 else 0 end) rejectedCount,
d.process_name,d.process_id
from wf_instance a
left join wf_his_task b on a.instance_id = b.instance_id
left join wf_task c on a.instance_id = c.instance_id
left join wf_process d on a.process_id = d.process_id
where 1=1

and (b.owner_id = #{ownerId,jdbcType=INTEGER} or c.owner_id = #{ownerId,jdbcType=INTEGER})

AND c.is_claim = 1

and d.create_time <![CDATA[ >= ]]> #{startDate} and d.create_time <![CDATA[ <= ]]> #{endDate}


and d.create_time <![CDATA[ >= ]]> #{defaultDate}

group by d.process_name,d.process_id
) a
group by process_name,process_id