解决方案 »
- select * from table按照什么排序啊
- 一个表,exp不出错,expdp出错,IO错误!!!!
- drop表的时候出错,用sys用户也不行,提示下面的错误
- 复杂的求和问题!!
- 在redhat9 上面装oracle 9i出现的java 问题,请高手指教!!!!!!!!!!
- oracle 10g?
- 无法加载 DLL“OraOps9.dll”: 找不到指定的模块
- 找出员工表中各月最后一天受雇的所有雇员???
- oracle7.3 和oracle9i 互导出现字符冲突,大家帮忙来看一下!
- ★★前途迷茫!!怎么样的水平才能找到ORACLE开发的工作????我要喝西北风了!!
- 静默安装oracle10g的问题
- oracle存储过程创建表并复制数据?求助高手
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>