select * from (select t1.orderid,t1.ordername,t2.product from 表一 t1,表二 t2 where t1.orderid=t2.orderid) t where exists(select null from (select t1.orderid,t1.ordername,t2.product from 表一 t1,表二 t2 where t1.orderid=t2.orderid) t3 where t3.ordername=t.ordername and t3.product not like '%a%')
SQL> with ordert as 2 ( 3 select 1 orderid, 'aa' ordername from dual union all 4 select 2 orderid, 'bb' ordername from dual union all 5 select 3 orderid, 'cc' ordername from dual 6 ) 7 ,product as 8 ( 9 select 1 id ,'a' product, 1 orderid from dual union all 10 select 2 id ,'b' product, 1 orderid from dual union all 11 select 3 id ,'c' product, 2 orderid from dual union all 12 select 4 id ,'d' product, 2 orderid from dual 13 ) 14 select distinct t.orderid, t.ordername 15 from ordert t, product k 16 where t.orderid not in (select b.orderid 17 from ordert b, product p 18 where b.orderid = p.orderid 19 and product like '%a%') 20 and k.orderid = t.orderid 21 ; ORDERID ORDERNAME ---------- --------- 2 bbSQL>
------小优化下。。 SQL> with ordert as 2 ( 3 select 1 orderid, 'aa' ordername from dual union all 4 select 2 orderid, 'bb' ordername from dual union all 5 select 3 orderid, 'cc' ordername from dual 6 ) 7 ,product as 8 ( 9 select 1 id ,'a' product, 1 orderid from dual union all 10 select 2 id ,'b' product, 1 orderid from dual union all 11 select 3 id ,'c' product, 2 orderid from dual union all 12 select 4 id ,'d' product, 2 orderid from dual 13 ) 14 select distinct t.orderid, t.ordername 15 from ordert t, product k 16 where t.orderid not in 17 (select p.orderid from product p where product like '%a%') 18 and k.orderid = t.orderid 19 ; ORDERID ORDERNAME ---------- --------- 2 bbSQL>
select * from
(select t1.orderid,t1.ordername,t2.product
from 表一 t1,表二 t2
where t1.orderid=t2.orderid) t
where exists(select null from
(select t1.orderid,t1.ordername,t2.product
from 表一 t1,表二 t2
where t1.orderid=t2.orderid) t3
where t3.ordername=t.ordername and t3.product not like '%a%')
SQL> with ordert as
2 (
3 select 1 orderid, 'aa' ordername from dual union all
4 select 2 orderid, 'bb' ordername from dual union all
5 select 3 orderid, 'cc' ordername from dual
6 )
7 ,product as
8 (
9 select 1 id ,'a' product, 1 orderid from dual union all
10 select 2 id ,'b' product, 1 orderid from dual union all
11 select 3 id ,'c' product, 2 orderid from dual union all
12 select 4 id ,'d' product, 2 orderid from dual
13 )
14 select distinct t.orderid, t.ordername
15 from ordert t, product k
16 where t.orderid not in (select b.orderid
17 from ordert b, product p
18 where b.orderid = p.orderid
19 and product like '%a%')
20 and k.orderid = t.orderid
21 ; ORDERID ORDERNAME
---------- ---------
2 bbSQL>
------小优化下。。
SQL> with ordert as
2 (
3 select 1 orderid, 'aa' ordername from dual union all
4 select 2 orderid, 'bb' ordername from dual union all
5 select 3 orderid, 'cc' ordername from dual
6 )
7 ,product as
8 (
9 select 1 id ,'a' product, 1 orderid from dual union all
10 select 2 id ,'b' product, 1 orderid from dual union all
11 select 3 id ,'c' product, 2 orderid from dual union all
12 select 4 id ,'d' product, 2 orderid from dual
13 )
14 select distinct t.orderid, t.ordername
15 from ordert t, product k
16 where t.orderid not in
17 (select p.orderid from product p where product like '%a%')
18 and k.orderid = t.orderid
19 ; ORDERID ORDERNAME
---------- ---------
2 bbSQL>