17,140
社区成员




-------实验证明,可以。。
SQL>
SQL> with A As
2 (
3 select 1 id from dual union all
4 select 2 id from dual union all
5 select 3 id from dual union all
6 select 4 id from dual union all
7 select 5 id from dual union all
8 select 6 id from dual union all
9 select 0 id from dual union all
10 select 7 id from dual
11 ),B as
12 (
13 select 10 id from dual union all
14 select 11 id from dual union all
15 select 12 id from dual union all
16 select 13 id from dual union all
17 select 1 id from dual union all
18 select 2 id from dual union all
19 select 3 id from dual
20 )
21 select a.*,b.* from a,b where a.id>=b.id(+)
22 /
ID ID
---------- ----------
7 3
7 2
7 1
6 3
6 2
6 1
5 3
5 2
5 1
4 3
4 2
4 1
3 3
3 2
3 1
2 2
2 1
1 1
0
19 rows selected
SQL>
--不好意思理解错了
--但是我的8i里确实可以显示啊
连接到:
Oracle8i Release 8.1.7.0.0 - Production
JServer Release 8.1.7.0.0 - Production
SQL> create table a as (
2 select 1 id from dual union all
3 select 9 id from dual);
表已创建。
SQL> create table b as(
2 select 6 id from dual union all
3 select 6 id from dual union all
4 select 6 id from dual union all
5 select 6 id from dual union all
6 select 6 id from dual union all
7 select 6 id from dual
8 )
9 /
表已创建。
SQL> select a.*,b.* from a,b where a.id<b.id(+)
2 /
ID ID
---------- ----------
1 6
1 6
1 6
1 6
1 6
1 6
9
已选择7行。
select * from V$version
-------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
with A As
(
select 1 id from dual union all
select 7 id from dual
),B as
(
select 5 id from dual
)
select a.*,b.* from a,b where a.id<=b.id(+)
----------------------------------------------
1 5
7 null
select * from V$version
------------------------------
1 Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
2 PL/SQL Release 8.1.7.0.0 - Production
3 CORE 8.1.7.0.0 Production
4 TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
5 NLSRTL Version 3.4.1.0.0 - Production
select * from tt1
-------------------------
WMD_MOR_NO WMD_OPSEQNO WMR_OPR_CODE WMD_ITM_CODE ITM_UNT_CODE WMD_ISSUEQTY
PMO101122042 1 物料裁剪 101-01-22991333 M 134.27
PMO101122042 9 覆膜壓合 PT-D26C-O2 PNL 463
select * from tt2
-------------------------
MOR_ID MOR_NO MOL_REQUESTQTY OP TOTALQTY
1 191624 PMO101122042 100 6 463
2 191624 PMO101122042 100 6 463
3 191624 PMO101122042 100 6 463
4 191624 PMO101122042 100 6 463
5 191624 PMO101122042 63 6 463
select * from tt1 a,tt2 b
where a.wmd_mor_no=b.mor_no and
a.wmd_opseqno <=b.op(+)
或者
select * from tt1 a,tt2 b
where a.wmd_mor_no=b.mor_no and
b.op(+)>=a.wmd_opseqno
------------------------------
WMD_MOR_NO WMD_OPSEQNO WMR_OPR_CODE WMD_ITM_CODE ITM_UNT_CODE WMD_ISSUEQTY MOR_ID MOR_NO MOL_REQUESTQTY OP TOTALQTY
1 PMO101122042 1 物料裁剪 101-01-22991333 M 134.27 191624 PMO101122042 100 6 463
2 PMO101122042 1 物料裁剪 101-01-22991333 M 134.27 191624 PMO101122042 100 6 463
3 PMO101122042 1 物料裁剪 101-01-22991333 M 134.27 191624 PMO101122042 100 6 463
4 PMO101122042 1 物料裁剪 101-01-22991333 M 134.27 191624 PMO101122042 100 6 463
5 PMO101122042 1 物料裁剪 101-01-22991333 M 134.27 191624 PMO101122042 63 6 463
为什么 tt1 中的PMO101122042 9 覆膜壓合 PT-D26C-O2 PNL 463 不出现在结果中呢?
--我也是这个版本的
SQL*Plus: Release 8.1.7.0.0 - Production on 星期三 12月 8 14:14:30 2010
(c) Copyright 2000 Oracle Corporation. All rights reserved.
连接到:
Oracle8i Release 8.1.7.0.0 - Production
JServer Release 8.1.7.0.0 - Production
SQL> select * from emp,dept
2 where emp.deptno(+)>dept.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ---------- --
7369 SMITH CLERK 7902 17-12月-80 800 20 10 ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 10 ACCOUNTING NEW Y
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 10 ACCOUNTING NEW Y
7566 JONES MANAGER 7839 02-4月 -81 2975 20 10 ACCOUNTING NEW YORK
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 10 ACCOUNTING
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 10 ACCOUNTING NEW Y
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 10 ACCOUNTING NEW YORK
7900 JAMES CLERK 7698 03-12月-81 950 30 10 ACCOUNTING NEW YORK
7902 FORD ANALYST 7566 03-12月-81 3000 20 10 ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 20 RESEARCH
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 20 RESEARCH DALLAS
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-12月-81 950 30 20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 test nanchang
已选择20行。