select a.order_quantity, a.deptname, a.status
from (
select count(mv.id) order_quantity, hr.deptname, 'exception' status
from hr_dept hr
left join mrp_wo_v mv
on mv.Manufact_Hr_dept_id = hr.id
and mv.plan_end_date > sysdate
and substr(mv.wo_date_str, 0, 4) = '2017'
and substr(mv.wo_date_str, 5, 2) = '12'
group by hr.deptname
union all
select count(mv.id) order_quantity, hr.deptname, 'normal' status
from hr_dept hr
left join mrp_wo_v mv
on mv.Manufact_Hr_dept_id = hr.id
and mv.plan_end_date < sysdate
and substr(mv.wo_date_str, 0, 4) = '2017'
and substr(mv.wo_date_str, 5, 2) = '12'
group by hr.deptname
) a
order by a.deptname, a.status;
问题:当 mv.plan_end_date 全部小于sysdate时,查不出excepiton的数据了,改怎么改造????要求数据必须成对存在,一个exception匹配一个normal