select vwxjddmonyea.create_date,vwxjddmonyea.DM_GROUP,view_xjj.xjjhj from
(view_xjj)
right join (vwxjddmonyea)
on (view_xjj.DM_GROUP=vwxjddmonyea.DM_GROUP)
3 个解决方案
#1
视图view_xjj 的代码
create or replace view view_xjj as
(select hj.create_date,
hj.DM_GROUP,
nvl(hj.xjjdx,0)+nvl(hj.xjjdb,0)+nvl(hj.xjjqq,0)+nvl(hj.xjjcx,0)+nvl(hj.xjjsq,0)+nvl(hj.xjjqj,0) xjjhj,
hj.xjjdx,
hj.xjjdb,
hj.xjjqq,
hj.xjjcx,
hj.xjjsq,
hj.xjjqj from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
hj.xjjdb,
hj.xjjqq,
hj.xjjcx,
hj.xjjsq,
hj.xjjqj,
nvl(bf.xjjbf,0) xjjbf from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
hj.xjjdb,
hj.xjjqq,
hj.xjjcx,
hj.xjjsq,
nvl(qj.xjjqj,0) xjjqj from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
hj.xjjdb,
hj.xjjqq,
hj.xjjcx,
nvl(sq.xjjsq,0) xjjsq from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
hj.xjjdb,
hj.xjjqq,
nvl(cx.xjjcx,0) xjjcx from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
hj.xjjdb,
nvl(qq.xjjqq,0) xjjqq
from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
nvl(db.xjjdb,0) xjjdb
from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
nvl(dx.xjjdx,0) xjjdx
from (select count(t.WORK_TEAM_NAME) xjjhj, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date
)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t where trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) hj left join
(select count(t.WORK_TEAM_NAME) xjjdx, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date
)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.GENEAL_WORKOVER_TYPE = '大修' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) dx on hj.create_date=dx.create_date and trim(hj.DM_GROUP)=trim(dx.DM_GROUP)) hj
left join
(select count(t.WORK_TEAM_NAME) xjjdb, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')
and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date
)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.GENEAL_WORKOVER_TYPE = '电泵' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) db on hj.create_date=db.create_date and hj.DM_GROUP=db.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjqq, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')
and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.GENEAL_WORKOVER_TYPE = '浅取' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) qq on hj.create_date=qq.create_date and hj.DM_GROUP=qq.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjcx, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.OPERATION_TYPE = '侧斜' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) cx on hj.create_date=cx.create_date and hj.DM_GROUP=cx.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjsq, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')
and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.OPERATION_TYPE = '深取' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) sq on hj.create_date=sq.create_date and hj.DM_GROUP=sq.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjqj, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.OPERATION_TYPE = '气井' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) qj on hj.create_date=qj.create_date and hj.DM_GROUP=qj.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjbf, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.GENEAL_WORKOVER_TYPE = '报废' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) bf on hj.create_date=bf.create_date and hj.DM_GROUP=bf.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjzj, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.OPERATION_TYPE = '钻井' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) zj on hj.create_date=zj.create_date and hj.DM_GROUP=zj.DM_GROUP)
create or replace view view_xjj as
(select hj.create_date,
hj.DM_GROUP,
nvl(hj.xjjdx,0)+nvl(hj.xjjdb,0)+nvl(hj.xjjqq,0)+nvl(hj.xjjcx,0)+nvl(hj.xjjsq,0)+nvl(hj.xjjqj,0) xjjhj,
hj.xjjdx,
hj.xjjdb,
hj.xjjqq,
hj.xjjcx,
hj.xjjsq,
hj.xjjqj from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
hj.xjjdb,
hj.xjjqq,
hj.xjjcx,
hj.xjjsq,
hj.xjjqj,
nvl(bf.xjjbf,0) xjjbf from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
hj.xjjdb,
hj.xjjqq,
hj.xjjcx,
hj.xjjsq,
nvl(qj.xjjqj,0) xjjqj from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
hj.xjjdb,
hj.xjjqq,
hj.xjjcx,
nvl(sq.xjjsq,0) xjjsq from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
hj.xjjdb,
hj.xjjqq,
nvl(cx.xjjcx,0) xjjcx from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
hj.xjjdb,
nvl(qq.xjjqq,0) xjjqq
from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
nvl(db.xjjdb,0) xjjdb
from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
nvl(dx.xjjdx,0) xjjdx
from (select count(t.WORK_TEAM_NAME) xjjhj, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date
)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t where trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) hj left join
(select count(t.WORK_TEAM_NAME) xjjdx, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date
)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.GENEAL_WORKOVER_TYPE = '大修' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) dx on hj.create_date=dx.create_date and trim(hj.DM_GROUP)=trim(dx.DM_GROUP)) hj
left join
(select count(t.WORK_TEAM_NAME) xjjdb, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')
and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date
)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.GENEAL_WORKOVER_TYPE = '电泵' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) db on hj.create_date=db.create_date and hj.DM_GROUP=db.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjqq, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')
and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.GENEAL_WORKOVER_TYPE = '浅取' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) qq on hj.create_date=qq.create_date and hj.DM_GROUP=qq.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjcx, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.OPERATION_TYPE = '侧斜' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) cx on hj.create_date=cx.create_date and hj.DM_GROUP=cx.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjsq, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')
and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.OPERATION_TYPE = '深取' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) sq on hj.create_date=sq.create_date and hj.DM_GROUP=sq.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjqj, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.OPERATION_TYPE = '气井' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) qj on hj.create_date=qj.create_date and hj.DM_GROUP=qj.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjbf, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.GENEAL_WORKOVER_TYPE = '报废' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) bf on hj.create_date=bf.create_date and hj.DM_GROUP=bf.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjzj, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.OPERATION_TYPE = '钻井' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) zj on hj.create_date=zj.create_date and hj.DM_GROUP=zj.DM_GROUP)
#2
视图vwxjddmonyea 的代码
create or replace view vwxjddmonyea as
select n.*,m.yeadx ,
m.yeadb,
m.yeaqq,
m.yeabf,
m.yeacx,
m.yeasq,
m.yeaqj from
(select * from vwxjddmon) n,
(select * from vwxjddyea) m
where m.create_date=n.create_date and m.dm_group=n.dm_group
create or replace view vwxjddmonyea as
select n.*,m.yeadx ,
m.yeadb,
m.yeaqq,
m.yeabf,
m.yeacx,
m.yeasq,
m.yeaqj from
(select * from vwxjddmon) n,
(select * from vwxjddyea) m
where m.create_date=n.create_date and m.dm_group=n.dm_group
#3
alter session set "_optimizer_filter_pred_pullup"=false;
alter system set "_optimizer_filter_pred_pullup"=false;
#1
视图view_xjj 的代码
create or replace view view_xjj as
(select hj.create_date,
hj.DM_GROUP,
nvl(hj.xjjdx,0)+nvl(hj.xjjdb,0)+nvl(hj.xjjqq,0)+nvl(hj.xjjcx,0)+nvl(hj.xjjsq,0)+nvl(hj.xjjqj,0) xjjhj,
hj.xjjdx,
hj.xjjdb,
hj.xjjqq,
hj.xjjcx,
hj.xjjsq,
hj.xjjqj from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
hj.xjjdb,
hj.xjjqq,
hj.xjjcx,
hj.xjjsq,
hj.xjjqj,
nvl(bf.xjjbf,0) xjjbf from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
hj.xjjdb,
hj.xjjqq,
hj.xjjcx,
hj.xjjsq,
nvl(qj.xjjqj,0) xjjqj from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
hj.xjjdb,
hj.xjjqq,
hj.xjjcx,
nvl(sq.xjjsq,0) xjjsq from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
hj.xjjdb,
hj.xjjqq,
nvl(cx.xjjcx,0) xjjcx from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
hj.xjjdb,
nvl(qq.xjjqq,0) xjjqq
from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
nvl(db.xjjdb,0) xjjdb
from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
nvl(dx.xjjdx,0) xjjdx
from (select count(t.WORK_TEAM_NAME) xjjhj, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date
)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t where trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) hj left join
(select count(t.WORK_TEAM_NAME) xjjdx, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date
)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.GENEAL_WORKOVER_TYPE = '大修' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) dx on hj.create_date=dx.create_date and trim(hj.DM_GROUP)=trim(dx.DM_GROUP)) hj
left join
(select count(t.WORK_TEAM_NAME) xjjdb, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')
and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date
)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.GENEAL_WORKOVER_TYPE = '电泵' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) db on hj.create_date=db.create_date and hj.DM_GROUP=db.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjqq, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')
and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.GENEAL_WORKOVER_TYPE = '浅取' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) qq on hj.create_date=qq.create_date and hj.DM_GROUP=qq.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjcx, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.OPERATION_TYPE = '侧斜' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) cx on hj.create_date=cx.create_date and hj.DM_GROUP=cx.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjsq, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')
and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.OPERATION_TYPE = '深取' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) sq on hj.create_date=sq.create_date and hj.DM_GROUP=sq.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjqj, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.OPERATION_TYPE = '气井' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) qj on hj.create_date=qj.create_date and hj.DM_GROUP=qj.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjbf, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.GENEAL_WORKOVER_TYPE = '报废' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) bf on hj.create_date=bf.create_date and hj.DM_GROUP=bf.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjzj, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.OPERATION_TYPE = '钻井' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) zj on hj.create_date=zj.create_date and hj.DM_GROUP=zj.DM_GROUP)
create or replace view view_xjj as
(select hj.create_date,
hj.DM_GROUP,
nvl(hj.xjjdx,0)+nvl(hj.xjjdb,0)+nvl(hj.xjjqq,0)+nvl(hj.xjjcx,0)+nvl(hj.xjjsq,0)+nvl(hj.xjjqj,0) xjjhj,
hj.xjjdx,
hj.xjjdb,
hj.xjjqq,
hj.xjjcx,
hj.xjjsq,
hj.xjjqj from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
hj.xjjdb,
hj.xjjqq,
hj.xjjcx,
hj.xjjsq,
hj.xjjqj,
nvl(bf.xjjbf,0) xjjbf from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
hj.xjjdb,
hj.xjjqq,
hj.xjjcx,
hj.xjjsq,
nvl(qj.xjjqj,0) xjjqj from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
hj.xjjdb,
hj.xjjqq,
hj.xjjcx,
nvl(sq.xjjsq,0) xjjsq from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
hj.xjjdb,
hj.xjjqq,
nvl(cx.xjjcx,0) xjjcx from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
hj.xjjdb,
nvl(qq.xjjqq,0) xjjqq
from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
hj.xjjdx,
nvl(db.xjjdb,0) xjjdb
from
(select hj.create_date,
hj.DM_GROUP,
hj.xjjhj,
nvl(dx.xjjdx,0) xjjdx
from (select count(t.WORK_TEAM_NAME) xjjhj, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date
)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t where trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) hj left join
(select count(t.WORK_TEAM_NAME) xjjdx, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date
)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.GENEAL_WORKOVER_TYPE = '大修' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) dx on hj.create_date=dx.create_date and trim(hj.DM_GROUP)=trim(dx.DM_GROUP)) hj
left join
(select count(t.WORK_TEAM_NAME) xjjdb, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')
and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date
)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.GENEAL_WORKOVER_TYPE = '电泵' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) db on hj.create_date=db.create_date and hj.DM_GROUP=db.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjqq, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')
and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.GENEAL_WORKOVER_TYPE = '浅取' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) qq on hj.create_date=qq.create_date and hj.DM_GROUP=qq.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjcx, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.OPERATION_TYPE = '侧斜' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) cx on hj.create_date=cx.create_date and hj.DM_GROUP=cx.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjsq, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')
and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.OPERATION_TYPE = '深取' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) sq on hj.create_date=sq.create_date and hj.DM_GROUP=sq.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjqj, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.OPERATION_TYPE = '气井' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) qj on hj.create_date=qj.create_date and hj.DM_GROUP=qj.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjbf, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.GENEAL_WORKOVER_TYPE = '报废' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) bf on hj.create_date=bf.create_date and hj.DM_GROUP=bf.DM_GROUP) hj
left join
(select count(t.WORK_TEAM_NAME) xjjzj, t.DM_GROUP, t.create_date
from (select *
from DH_DIRECT_WORKOVER_DAILY a
where a.howell_common_name is not null
and a.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and
a.work_team_name not in
(select work_team_name
from dh_direct_workover_daily b
where b.howell_common_name is null and a.create_date=b.create_date)
union
select *
from DH_DIRECT_WORKOVER_DAILY c
where c.howell_common_name is null and c.create_date=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) t
where t.OPERATION_TYPE = '钻井' and trim(t.DM_GROUP) is not null
group by t.DM_GROUP, t.create_date) zj on hj.create_date=zj.create_date and hj.DM_GROUP=zj.DM_GROUP)
#2
视图vwxjddmonyea 的代码
create or replace view vwxjddmonyea as
select n.*,m.yeadx ,
m.yeadb,
m.yeaqq,
m.yeabf,
m.yeacx,
m.yeasq,
m.yeaqj from
(select * from vwxjddmon) n,
(select * from vwxjddyea) m
where m.create_date=n.create_date and m.dm_group=n.dm_group
create or replace view vwxjddmonyea as
select n.*,m.yeadx ,
m.yeadb,
m.yeaqq,
m.yeabf,
m.yeacx,
m.yeasq,
m.yeaqj from
(select * from vwxjddmon) n,
(select * from vwxjddyea) m
where m.create_date=n.create_date and m.dm_group=n.dm_group
#3
alter session set "_optimizer_filter_pred_pullup"=false;
alter system set "_optimizer_filter_pred_pullup"=false;