ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL子查询

SQL子查询

原创 Linux操作系统 作者:lawzjf 时间:2007-04-02 00:00:00 0 删除 编辑

1. 包含在select子句中的子查询:

SQL> select dname,(select max(sal) from emp where deptno=dept.deptno) max_sal
2 from dept
3 /

DNAME MAX_SAL
-------------- ----------
ACCOUNTING 5000
RESEARCH 3000
SALES 2850
OPERATIONS

可以在显示每行指定列的同时,显示分组函数。


与下面查询有类似效果,但下面查询是显示的deptno列:

SQL> select deptno,max(sal) from emp
2 group by deptno
3 /

DEPTNO MAX(SAL)
---------- ----------
30 2850
20 3000
10 5000

与下面示例也有类似的效果,但这里的第一列只显示了三行:

SQL> select d.dname, max(e.sal)
2 from emp e, dept d
3 where e.deptno=d.deptno
4 group by dname
5 /

DNAME MAX(E.SAL)
-------------- ----------
ACCOUNTING 5000
RESEARCH 3000
SALES 2850

下面示例可以代替emp与dept两个表的连接查询:

SQL> select ename,(select dname from dept where deptno=emp.deptno) dname
2 from emp
3 /

ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH

下面是与上面查询相同效果的表的连接查询:

SQL> select e.ename,d.dname
2 from emp e, dept d
3 where e.deptno=d.deptno
4 /

ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH

下面select语句中的子查询更能体现其作用:

SQL> select ename, sal, (select avg(sal) from emp) avgsal
2 from emp
3 /

SQL> select ename, sal,
2 (select avg(sal) from emp where emp.deptno=emp1.deptno) avgsal
3 from emp emp1
4 /

2. 包含在where子句中的子查询:

SQL> select dname
2 from dept
3 where deptno=(select deptno from emp)
4 /
where deptno=(select deptno from emp)
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one ro

当where条件中使用=、 <、 >、 !=、 >=、 <=时,要保证子查询返回的结果唯一,否则可以使用in,或者使用any、some、all等前缀处理:

SQL> select dname
2 from dept
3 where deptno in (select deptno from emp)
4 /

DNAME
--------------
RESEARCH
SALES
ACCOUNTING

SQL> select ename
2 from emp
3 where sal>all(select sal from emp where deptno=30)
4 /

ENAME
----------
JONES
SCOTT
KING
FORD

SQL> select dname
2 from dept
3 where deptno in
4 (
5 select deptno from emp
6 group by deptno
7 having count(empno)>3
8 )
9 /

DNAME
--------------
SALES
RESEARCH

3. 包含在from子句中的子查询:

统计emp表中,每年入职的员工人数:

SQL> select to_char(hiredate,'yyyy') hireYear,count(empno)
2 from emp
3 group by hireYear
4 /
group by hireYear
*
ERROR at line 3:
ORA-00904: "HIREYEAR": invalid identifier

因为from子句最先处理,然后是group by子句,最后是select子句,所以上述group by子句中使用的列别名还不可见,从而出现错误,我们可以改为在from子句中使用子查询解决:

SQL> select hireYear,count(empno)
2 from (select to_char(hiredate,'yyyy') hireYear, empno from emp)
3 group by hireYear
4 /

HIRE COUNT(EMPNO)
---- ------------
1987 2
1980 1
1982 1
1981 10

也可以使用表别名,使得语法更通顺:

SQL> select hireYear,count(empno)
2 from (select to_char(hiredate,'yyyy') hireYear, empno from emp) d
3 group by hireYear
4 /

HIRE COUNT(EMPNO)
---- ------------
1987 2
1980 1
1982 1
1981 10

另外子查询也可以嵌套命名:

SQL> select hireYear,count(empno)
2 from
3 (select to_char(hiredate,'yyyy') hireYear,empno
4 from (select * from emp) d1
5 ) d2
6 group by hireYear
7 /

HIRE COUNT(EMPNO)
---- ------------
1987 2
1980 1
1982 1
1981 10

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/37724/viewspace-152592/,如需转载,请注明出处,否则将追究法律责任。

上一篇: redhat的etc目录
请登录后发表评论 登录
全部评论

注册时间:2008-01-18

  • 博文量
    279
  • 访问量
    932078