Oracle SQL ANY和ALL语句

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语句

标签：