sql

select
parentLocation.id,

parentLocation.name,
(sum(ifnull(vmStatus.running_count,0))) as running_count,
(sum(ifnull(vmStatus.stopped_count,0))) as stopped_count,
(sum(ifnull(vmStatus.hibernated_count,0))) as hibernated_count,
(sum(ifnull(vmStatus.other_count,0))) as other_count,


parentLocation.region_id

from

t_res_azone parentLocation

left join

(select region_id,azone_id,id,collect_time from t_res_pod outerPod where collect_time = (select min(collect_time) from t_res_pod innerPod where innerPod.region_id = outerPod.region_id)) pod

on pod.azone_id = parentLocation.id

left outer join t_cap_vm_status vmStatus
on vmStatus.res_id = pod.id
and vmStatus.collect_time = #{collectTime}
where
parentLocation.collect_time =

(select min(collect_time) from t_res_azone where region_id = parentLocation.region_id)

GROUP BY parentLocation.id