典型的写法
select empno, ename, sal, dname, loc
from emp, dept
where emp.deptno = dept.deptno
子查询的写法(dname和loc这里是分开写的,他们不能写到一个子查询里面)
select empno,
ename,
sal,
(select dname from dept where emp.deptno = dept.deptno) dname,
(select loc from dept where emp.deptno = dept.deptno) loc
from emp
这里的emp和dept表都是最系统自带的,没有改动过
这里注意dept表的结构,deptno和dname、loc都是一一对应的
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
-----------------------------------------------------------------------------------
进行如下改动
SQL> create table deptx as select * from dept;
SQL> insert into deptx values(10,'xxx','yyy');
SQL> select * from deptx;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
10 xxx yyy
再次执行如下SQL会报错 ORA-01427:d单行子查询返回多个行,原因在于这里的deptno=10时,dname出现的是2个值了,
而在和emp做连接的时候,ename的deptno等于10时不知道是选哪个dname,也就是说还是做连接的deptno出了问题,
deptno 在一边对应一个值,在另一边对应2个值
select deptno,
ename,
sal,
(select dname from deptx where emp.deptno = deptx.deptno) dname,
(select loc from deptx where emp.deptno = deptx.deptno) loc
from emp
改写如下
select deptno,
ename,
sal,
(select dname from deptx where emp.deptno = deptx.deptno and rownum<=1) dname,
(select loc from deptx where emp.deptno = deptx.deptno and rownum<=1) loc
from emp
DEPTNO ENAME SAL DNAME LOC
------ ---------- --------- -------------- -------------
20 SMITH 99.00 RESEARCH DALLAS
30 ALLEN 99.00 SALES CHICAGO
30 WARD 99.00 SALES CHICAGO
20 JONES 99.00 RESEARCH DALLAS
30 MARTIN 99.00 SALES CHICAGO
30 BLAKE 99.00 SALES CHICAGO
10 CLARK 99.00 ACCOUNTING NEW YORK
20 SCOTT 99.00 RESEARCH DALLAS
10 KING 99.00 ACCOUNTING NEW YORK
30 TURNER 99.00 SALES CHICAGO
20 ADAMS 99.00 RESEARCH DALLAS
30 JAMES 99.00 SALES CHICAGO
20 FORD 99.00 RESEARCH DALLAS
10 MILLER 99.00 ACCOUNTING NEW YORK
14 rows selected
这里看可以从结果看出10返回的是对应的NEW YORK而不是yyy
出现问题的在于deptno做连接这个地方,emp的deptno是外键,它的参照列是dept的deptno,dept.deptno作为人家外键的参照列应该是主键约束,通俗的说
dept是emp的维度表,那么这里出现了一个纬度号对应了多个纬度列
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10678398/viewspace-695728/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10678398/viewspace-695728/