有这么一张部门表:
create table dept( id number(3), name nvarchar2(20), primary key(id) );
可以这样给它充值:
insert into dept(id,name) values(1,'市场部'); insert into dept(id,name) values(2,'管理部');
还有这么一张雇员表:
create table emp( id number(3), name nvarchar2(20), deptid number(3), primary key(id) );
可以这样给它充值:
insert into emp(id,name,deptid) values(1,'鲁智深',1); insert into emp(id,name,deptid) values(2,'林冲',1); insert into emp(id,name,deptid) values(3,'武松',1); insert into emp(id,name,deptid) values(4,'宋江',2); insert into emp(id,name,deptid) values(5,'吴用',2); insert into emp(id,name,deptid) values(6,'晁盖',2);
如果要把雇员名字前面加上部门前缀显示出来,可以采用这种方式:
select (select name from dept where id=emp.deptid)||'-'||name as newName from emp
执行结果是:
SQL> select (select name from dept where id=emp.deptid)||'-'||name as newName from 2 emp; NEWNAME -------------------------------------------------------------------------------- 市场部-鲁智深 市场部-林冲 市场部-武松 管理部-宋江 管理部-吴用 管理部-晁盖 已选择6行。
但之后,如果想对newName进行模糊查询,会出现ORA-00904: "NEWNAME": 标识符无效
SQL> select (select name from dept where id=emp.deptid)||'-'||name as newName from 2 emp where newName like '%市场部%'; emp where newName like '%市场部%' * 第 2 行出现错误: ORA-00904: "NEWNAME": 标识符无效
出现这个错误的原因是:where子句先于select子句执行,因此where子句中出现select子句中新定义的字段自然不行。
要想对newName进行模糊查询,可以把
select (select name from dept where id=emp.deptid)||'-'||name as newName from emp
作为结果集,把条件写在外面,再查询一次,如下:
select * from (select (select name from dept where id=emp.deptid)||'-'||name as newName from emp) a where a.newName like '%市场部%'
往后就可以随便查了,如下:
SQL> select * from 2 (select (select name from dept where id=emp.deptid)||'-'||name as newName from 3 emp) a 4 where a.newName like '%市场部%'; NEWNAME -------------------------------------------------------------------------------- 市场部-鲁智深 市场部-林冲 市场部-武松 SQL> select * from 2 (select (select name from dept where id=emp.deptid)||'-'||name as newName from 3 emp) a 4 where a.newName like '%鲁%'; NEWNAME -------------------------------------------------------------------------------- 市场部-鲁智深 SQL> select * from 2 (select (select name from dept where id=emp.deptid)||'-'||name as newName from 3 emp) a 4 where a.newName like '%部-鲁%'; NEWNAME -------------------------------------------------------------------------------- 市场部-鲁智深
END