select子查询

最新推荐文章于 2025-01-21 16:24:11 发布

转载最新推荐文章于 2025-01-21 16:24:11 发布 · 162 阅读

本文探讨了SQL中使用子查询代替表连接时遇到的问题，特别是当子查询返回多行时如何通过限制返回行数来解决ORA-01427错误。通过对部门表进行修改并插入重复记录，演示了如何正确处理一对多的关系。

典型的写法
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