比如说:
select
b.*
(select col from (select col from table3 c where c.key=a.key order by XXX desc) where rownum=1)
from table1 a,table2 b where ..... ;
做2个表的连接操作,但是还需要取得第三张表中信息,于是用a.key去取,本来是可以,可是后来需要添加一个查询最新记录的功能,只能使用诸如 select key (select key from table order by XXX desc) where rownum=1; 这种办法,但是问题随之而来,运行时候,报a.key无法取得。
select
b.*
(select col from (select col from table3 c where c.key=a.key order by XXX desc) where rownum=1)
from table1 a,table2 b where ..... ;
做2个表的连接操作,但是还需要取得第三张表中信息,于是用a.key去取,本来是可以,可是后来需要添加一个查询最新记录的功能,只能使用诸如 select key (select key from table order by XXX desc) where rownum=1; 这种办法,但是问题随之而来,运行时候,报a.key无法取得。
select a.tmh,
(select hphm||'('||hpzl||')' from (select hphm,hpzl from cl.lzd where xh=a.xh and substr(yw,1,1)='B' order by slrq desc) where rownum=1) bz
from cl.lzd a,cl.ylr b
where a.tmh=b.tmh and a.tmh='0801085863';
红色是报错的地方。
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Productionscott@ORA1> select b.*
2 ,(
3 select empno
4 from emp C
5 where C.deptno = B.deptno
6 and rownum = 1
7 )
8 from emp A, dept B
9 where A.deptno = B.deptno; DEPTNO DNAME LOC (SELECTEMPNOFROMEMPCWHEREC.DEPTNO=B.DEPTNOANDROWNUM=1)
---------- -------------- ------------- ------------------------------------------------------
20 RESEARCH DALLAS 7369
30 SALES CHICAGO 7499
30 SALES CHICAGO 7499
20 RESEARCH DALLAS 7369
30 SALES CHICAGO 7499
30 SALES CHICAGO 7499
10 ACCOUNTING NEW YORK 7782
20 RESEARCH DALLAS 7369
10 ACCOUNTING NEW YORK 7782
30 SALES CHICAGO 7499
20 RESEARCH DALLAS 7369
30 SALES CHICAGO 7499
20 RESEARCH DALLAS 7369
10 ACCOUNTING NEW YORK 7782已选择14行。scott@ORA1> select b.*
2 ,(select empno
3 from (
4 select empno
5 from emp C
6 where C.deptno = B.deptno
7 order by C.sal desc
8 ) e
9 where rownum = 1
10 ) x
11 from emp A, dept B
12 where A.deptno = B.deptno;
where C.deptno = B.deptno
*
第 6 行出现错误:
ORA-00904: "B"."DEPTNO": invalid identifier