create or replace view view_cj_qy_wcy as select id,qymc,yyzzzch,zzjgdm,qylx,qyzs,dept_simple_name from C_QYJBXX where yyzzzch not in (select distinct yyzzzch from C_QY_GCYYXX union select yyzzzch from CJ_QY_ZZJBXX union select yyzzzch from CJ_QY_SGXKZXX union select yyzzzch from CJ_QY_GCYJXX union select yyzzzch from CJ_QY_LHXWJLXX union select yyzzzch from CJ_QY_XZCFXX union select yyzzzch from CJ_QY_XZQZXX union select yyzzzch from CJ_QY_XZCJDXX union select yyzzzch from CJ_QY_XYPJCXQY );
你好,上面的是我的视图, 系统中的表是通过系统自动建的,视图也是自动建、更新的。 我实在是找不到好的方法啊。create or replace view view_cj_qy_wcy as select id,qymc,yyzzzch,zzjgdm,qylx,qyzs,dept_simple_name from C_QYJBXX where yyzzzch not in (select distinct yyzzzch from C_QY_GCYYXX union select yyzzzch from CJ_QY_ZZJBXX union select yyzzzch from CJ_QY_SGXKZXX union select yyzzzch from CJ_QY_GCYJXX union select yyzzzch from CJ_QY_LHXWJLXX union select yyzzzch from CJ_QY_XZCFXX union select yyzzzch from CJ_QY_XZQZXX union select yyzzzch from CJ_QY_XZCJDXX union select yyzzzch from CJ_QY_XYPJCXQY );
用not in子查询就不用去重了 去掉distinct union 改为union all select yyzzzch from C_QY_GCYYXX union all select yyzzzch from CJ_QY_ZZJBXX
create or replace view view_cj_qy_wcy as
select id,qymc,yyzzzch,zzjgdm,qylx,qyzs,dept_simple_name from C_QYJBXX
where yyzzzch not in
(select distinct yyzzzch from C_QY_GCYYXX
union select yyzzzch from CJ_QY_ZZJBXX
union select yyzzzch from CJ_QY_SGXKZXX
union select yyzzzch from CJ_QY_GCYJXX
union select yyzzzch from CJ_QY_LHXWJLXX
union select yyzzzch from CJ_QY_XZCFXX
union select yyzzzch from CJ_QY_XZQZXX
union select yyzzzch from CJ_QY_XZCJDXX
union select yyzzzch from CJ_QY_XYPJCXQY );
不用not in和not exists
那用什么
难道用left join或minus
那会更慢问题不是not in、not exists
应该是你关联的条件
把详细代码贴出来,大家才能帮你解决
你好,上面的是我的视图, 系统中的表是通过系统自动建的,视图也是自动建、更新的。
我实在是找不到好的方法啊。create or replace view view_cj_qy_wcy as
select id,qymc,yyzzzch,zzjgdm,qylx,qyzs,dept_simple_name from C_QYJBXX
where yyzzzch not in
(select distinct yyzzzch from C_QY_GCYYXX
union select yyzzzch from CJ_QY_ZZJBXX
union select yyzzzch from CJ_QY_SGXKZXX
union select yyzzzch from CJ_QY_GCYJXX
union select yyzzzch from CJ_QY_LHXWJLXX
union select yyzzzch from CJ_QY_XZCFXX
union select yyzzzch from CJ_QY_XZQZXX
union select yyzzzch from CJ_QY_XZCJDXX
union select yyzzzch from CJ_QY_XYPJCXQY );
去掉distinct
union 改为union all
select yyzzzch from C_QY_GCYYXX
union all select yyzzzch from CJ_QY_ZZJBXX