现有两个表
CREATE TABLE subInfoTab (
subID NUMBER(9) DEFAULT 0 NOT NULL,
DeptID NUMBER(9) DEFAULT 0 NOT NULL,
subName VARCHAR2(17) DEFAULT 0 NOT NULL);
ALTER TABLE subInfoTab ADD (
PRIMARY KEY ( subID ));
CREATE TABLE deptTab (
DeptID NUMBER(9) DEFAULT 0 NOT NULL,
deptName VARCHAR2(17) DEFAULT 0 NOT NULL);
ALTER TABLE deptTab ADD (
PRIMARY KEY ( DeptID ));
下面的子查询:
select a.subid,a.subname,
(select deptName from depttab b where b.deptid=a.deptid) deptName
from subInfoTab a;
没有问题,可以执行但是多了一层嵌套之后就不行了,编译不过,写法如下:
select a.subid,a.subname,
(select deptname from (select deptName from depttab b where b.deptid=a.deptid)) deptName
from subInfoTab a;执行时提示ORA-00904: "A"."DEPTID": 无效的标识符
百思不得其解,万望高手指点
CREATE TABLE subInfoTab (
subID NUMBER(9) DEFAULT 0 NOT NULL,
DeptID NUMBER(9) DEFAULT 0 NOT NULL,
subName VARCHAR2(17) DEFAULT 0 NOT NULL);
ALTER TABLE subInfoTab ADD (
PRIMARY KEY ( subID ));
CREATE TABLE deptTab (
DeptID NUMBER(9) DEFAULT 0 NOT NULL,
deptName VARCHAR2(17) DEFAULT 0 NOT NULL);
ALTER TABLE deptTab ADD (
PRIMARY KEY ( DeptID ));
下面的子查询:
select a.subid,a.subname,
(select deptName from depttab b where b.deptid=a.deptid) deptName
from subInfoTab a;
没有问题,可以执行但是多了一层嵌套之后就不行了,编译不过,写法如下:
select a.subid,a.subname,
(select deptname from (select deptName from depttab b where b.deptid=a.deptid)) deptName
from subInfoTab a;执行时提示ORA-00904: "A"."DEPTID": 无效的标识符
百思不得其解,万望高手指点
用外连接吧,类似于:select a.subid,a.subname,b.depttab
from subInfoTab a,depttab b where a.deptid=b.deptid;
select a.subid,a.subname,b.depttab
from subInfoTab a,depttab b where a.deptid=b.deptid(+);
(select deptname from (select deptName from depttab b,subInfoTab where b.deptid=subInfoTab .deptid)) deptName
from subInfoTab a;
select max(substr(SYS_CONNECT_BY_PATH(deptName, ','),2)) from
(
select deptName from depttab b where b.deptid=a.deptid)
) start with rn=1 connect by rn=rownum;
可是这样就会编译不过,执行时提示ORA-00904: "A"."DEPTID": 无效的标识符
select a.subid,a.subname,
(select max(substr(SYS_CONNECT_BY_PATH(deptName, ','),2)) from depttab b where b.deptid=a.deptid start with rn=1 connect by rn=rownum ) deptName
from subInfoTab a;start with rn=1 connect by rn=rownum 这部分好像有些问题(感觉应该在b中定义rn,然后connect by rn=rn-1),不知道你的rn和rownum想要怎么处理,修改一下应该就可以了