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
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
2、把 and substr(mv.wo_date_str, 0, 4) = '2017' and substr(mv.wo_date_str, 5, 2) = '12' 改为 and mv.wo_date_str like '201712%'会提高效率。
就是想在不满足大于sysdate的情况下,拼出exception的数据,该怎么改造语句能达到一个normal匹配一个exception的语句,求指教!
当 mv.plan_end_date 全部小于sysdate时,不满足mv.plan_end_date > sysdate ,第一个Select肯定取不到数据。
预期的结果如下:
数量 部门 状态
0 21车间 exception
260 21车间 normal
0 23车间 exception
176 23车间 normal
.....
......
and substr(mv.wo_date_str, 0, 4) = '2017'
and substr(mv.wo_date_str, 5, 2) = '12'
没有同时符合这三个条件的数据吧、还有
and substr(mv.wo_date_str, 0, 4) = '2017'
and substr(mv.wo_date_str, 5, 2) = '12' 这个条件可以换成to_char(mv.wo_date_str,'yyyy-mm')='2017-12'