一个问题:子表通过一个非主键属性排除主键所在的所有行?
比如:create table tmp_main
(pk_field integer);
insert into tmp_main(pk_field) values(1);
insert into tmp_main(pk_field) values(2);
commit;with t_detail as(
select 1 as pk_field,'0' as flag_field from dual
union all
select 1 as pk_field,'0' as flag_field from dual
union all
select 1 as pk_field,'1' as flag_field from dual
union all
select 1 as pk_field,'0' as flag_field from dual
union all
select 2 as pk_field,'0' as flag_field from dual
union all
select 2 as pk_field,'0' as flag_field from dual
)t_detail表里有flag_field = '1'的行,我想把满足这个条件的主键值的所有过滤掉select b.* FROM tmp_main a,t_detail b where a.pk_field = b.pk_field and ...想要显示的结果是:
pk_field flag_field
2 0
2 0请热心朋友帮忙,非常感谢!
比如:create table tmp_main
(pk_field integer);
insert into tmp_main(pk_field) values(1);
insert into tmp_main(pk_field) values(2);
commit;with t_detail as(
select 1 as pk_field,'0' as flag_field from dual
union all
select 1 as pk_field,'0' as flag_field from dual
union all
select 1 as pk_field,'1' as flag_field from dual
union all
select 1 as pk_field,'0' as flag_field from dual
union all
select 2 as pk_field,'0' as flag_field from dual
union all
select 2 as pk_field,'0' as flag_field from dual
)t_detail表里有flag_field = '1'的行,我想把满足这个条件的主键值的所有过滤掉select b.* FROM tmp_main a,t_detail b where a.pk_field = b.pk_field and ...想要显示的结果是:
pk_field flag_field
2 0
2 0请热心朋友帮忙,非常感谢!
with t_detail as(
select 1 as pk_field,'0' as flag_field from dual
union all
select 1 as pk_field,'0' as flag_field from dual
union all
select 1 as pk_field,'1' as flag_field from dual
union all
select 1 as pk_field,'0' as flag_field from dual
union all
select 2 as pk_field,'0' as flag_field from dual
union all
select 2 as pk_field,'0' as flag_field from dual
)
select b.* FROM t_detail b where not exists (select 1 from tmp_main t where b.flag_field=t.pk_field)
也就是只要pk_field = 2的值。
with t_detail as(
select 1 as pk_field,'0' as flag_field from dual
union all
select 1 as pk_field,'0' as flag_field from dual
union all
select 1 as pk_field,'1' as flag_field from dual
union all
select 1 as pk_field,'0' as flag_field from dual
union all
select 2 as pk_field,'0' as flag_field from dual
union all
select 2 as pk_field,'0' as flag_field from dual
)
select b.* FROM t_detail b where not exists (select 1 from tmp_main t where b.pk_field=t.pk_field)
with t_detail as(
select 1 as pk_field,'0' as flag_field from dual
union all
select 1 as pk_field,'0' as flag_field from dual
union all
select 1 as pk_field,'1' as flag_field from dual
union all
select 1 as pk_field,'0' as flag_field from dual
union all
select 2 as pk_field,'0' as flag_field from dual
union all
select 2 as pk_field,'0' as flag_field from dual
)
select b.* FROM t_detail b where not exists (select 1 from t_detail t,tmp_main t2 where b.pk_field=t.pk_field and t2.pk_field=t.flag_field)
select b.* FROM tmp_main a,
(select pk_field,flag_field FROM (select pk_field,flag_field,count(*) over() as c1,count(*) over(order by pk_field,flag_field) as c2 from t_detail) t where t.c1=t.c2) b
where a.pk_field = b.pk_field非常感谢,还是您的方法简单。结帖。