Oracle SQL ANY和ALL语句
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle SQL ANY和ALL语句，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4921字，纯文字阅读大概需要8分钟。
内容图文
select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
SQL> select * from emp order by deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7839 KING PRESIDENT 1981/11/17 5000.00 10
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
14 rows selected
SQL> select ename,deptno,sal from emp where sal > any (select sal from emp where deptno=10);
ENAME DEPTNO SAL
---------- ------ ---------
KING 10 5000.00
FORD 20 3000.00
SCOTT 20 3000.00
JONES 20 2975.00
BLAKE 30 2850.00
CLARK 10 2450.00
ALLEN 30 1600.00
TURNER 30 1500.00
8 rows selected
SQL> select ename,deptno,sal from emp where sal > all (select sal from emp where deptno=10);
ENAME DEPTNO SAL
---------- ------ ---------
SQL> select ename,deptno,sal from emp where sal > all (select sal from emp where deptno=20);
ENAME DEPTNO SAL
---------- ------ ---------
KING 10 5000.00
SQL>
SQL> select ename,deptno,sal from emp where sal < all (select sal from emp where deptno=10); ENAME DEPTNO SAL ---------- ------ --------- WARD 30 1250.00 MARTIN 30 1250.00 ADAMS 20 1100.00 JAMES 30 950.00 SMITH 20 800.00 SQL> SQL> SQL> SQL> select ename,deptno,sal from emp where sal < any (select sal from emp where deptno=10); ENAME DEPTNO SAL ---------- ------ --------- SMITH 20 800.00 JAMES 30 950.00 ADAMS 20 1100.00 WARD 30 1250.00 MARTIN 30 1250.00 MILLER 10 1300.00 TURNER 30 1500.00 ALLEN 30 1600.00 CLARK 10 2450.00 BLAKE 30 2850.00 JONES 20 2975.00 SCOTT 20 3000.00 FORD 20 3000.00 13 rows selected SQL> select sal from emp where deptno=10 2 ; SAL --------- 2450.00 5000.00 1300.00 SQL> select ename,deptno,sal from emp where sal < any (select sal from emp where deptno=20); ENAME DEPTNO SAL ---------- ------ --------- SMITH 20 800.00 JAMES 30 950.00 ADAMS 20 1100.00 WARD 30 1250.00 MARTIN 30 1250.00 MILLER 10 1300.00 TURNER 30 1500.00 ALLEN 30 1600.00 CLARK 10 2450.00 BLAKE 30 2850.00 JONES 20 2975.00 11 rows selected SQL> select sal from emp where deptno=20; SAL --------- 800.00 2975.00 3000.00 1100.00 3000.00 SQL> select ename,deptno,sal from emp where sal < all(select sal from emp where deptno=20); ENAME DEPTNO SAL ---------- ------ --------- SQL>
<ANY是比最大的还要小
>ANY是比最小的还要大
<ALL是比所有值都要小，即比最小的还要小
>ALL是比所有值都要大，即比最大的值还要大
Oracle SQL ANY和ALL语句
标签：
本文系统来源：http://www.cnblogs.com/rusking/p/4680436.html
内容总结
以上是互联网集市为您收集整理的Oracle SQL ANY和ALL语句全部内容，希望文章能够帮你解决Oracle SQL ANY和ALL语句所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】