当前位置:数据库 > Oracle >>

Oracle数据的排序:(order by)

order by 默认情况下是升序排列
SQL> select * from dept;(deptno默认的是升序排列)

  DEPTNO DNAME  LOC
---------- -------------- -------------
  10 ACCOUNTING   NEW YORK
  20 RESEARCH   DALLAS
  30 SALES  CHICAGO
  40 OPERATIONS   BOSTON
deptno是降序排列
SQL>select * from dept order by desc;
默认的:
SQL>select empno ,ename from emp;
根据empno升序排列
SQL>select empno,ename from emp order by empno;
(对empno)升序排列
SQL>select ename,empno from emp order by empno asc;
先进行过滤,在进行排序(除deptno为10的部门)
SQL>select ename,empno from emp where deptno<>10 order by empno asc; deptno进行升序排列
SQL>select ename,sal,deptno from emp order by deptno asc;
deptno进行升序排列的前提下,ename进行降序排列
SQL>select ename,sal,deptno from emp order by deptno asc,ename desc;
SQL> select * from dept;(查询dept)

  DEPTNO DNAME  LOC
---------- -------------- -------------
  10 ACCOUNTING   NEW YORK
  20 RESEARCH   DALLAS
  30 SALES  CHICAGO
  40 OPERATIONS   BOSTON

SQL> select * from dept order by deptno desc;(对dept降序排列)

  DEPTNO DNAME  LOC
---------- -------------- -------------
  40 OPERATIONS   BOSTON
  30 SALES  CHICAGO
  20 RESEARCH   DALLAS
  10 ACCOUNTING   NEW YORK

SQL> select empno,ename from emp;(查询emp表中的empno,ename,)

 EMPNO ENAME
---------- ----------
  7369 SMITH
  7499 ALLEN
  7521 WARD
  7566 JONES
  7654 MARTIN
  7698 BLAKE
  7782 CLARK
  7788 SCOTT
  7839 KING
  7844 TURNER
  7876 ADAMS

 EMPNO ENAME
---------- ----------
  7900 JAMES
  7902 FORD
  7934 MILLER

已选择14行。

SQL> select empno,ename from emp order by empno;(对empno进行升序排列)

 EMPNO ENAME
---------- ----------
  7369 SMITH
  7499 ALLEN
  7521 WARD
  7566 JONES
  7654 MARTIN
  7698 BLAKE
  7782 CLARK
  7788 SCOTT
  7839 KING
  7844 TURNER
  7876 ADAMS

 EMPNO ENAME
---------- ----------
  7900 JAMES
  7902 FORD
  7934 MILLER

已选择14行。
(对雇员编号进行升序排列)
SQL> select empno ,ename from emp order by empno asc;(升序排列)

 EMPNO ENAME
---------- ----------
  7369 SMITH
  7499 ALLEN
  7521 WARD
  7566 JONES
  7654 MARTIN
  7698 BLAKE
  7782 CLARK
  7788 SCOTT
  7839 KING
  7844 TURNER
  7876 ADAMS

 EMPNO ENAME
---------- ----------
  7900 JAMES
  7902 FORD
  7934 MILLER

已选择14行。
(查询10号部门的雇员编号,姓名,并升序排列)
SQL> select empno,ename from emp where deptno <>10 order by empno asc;

 EMPNO ENAME
---------- ----------
  7369 SMITH
  7499 ALLEN
  7521 WARD
  7566 JONES
  7654 MARTIN
  7698 BLAKE
  7788 SCOTT
  7844 TURNER
  7876 ADAMS
  7900 JAMES
  7902 FORD

已选择11行。
(查询雇员姓名,雇员编号,和薪水,依据部门号升序)
SQL> select ename,sal,deptno from emp order by deptno asc;

ENAME   SAL   DEPTNO
---------- ---------- ----------
CLARK  2450   10
KING   5000   10
MILLER   1300   10
JONES  2975   20
FORD   3000   20
ADAMS  1100   20
SMITH   800   20
SCOTT  3000   20
WARD   1250   30
TURNER   1500   30
ALLEN  1600   30

ENAME   SAL   DEPTNO
---------- ---------- ----------
JAMES   950   30
BLAKE  2850   30
MARTIN   1250   30

已选择14行。

SQL> select ename,sal,deptno from emp order by deptno asc,ename desc;

ENAME   SAL   DEPTNO
---------- ---------- ----------
MILLER   1300   10
KING   5000   10
CLARK  2450   10
SMITH   800   20
SCOTT  3000   20
JONES  2975   20
FORD   3000   20
ADAMS  1100   20
WARD   1250   30
TURNER   1500   30
MARTIN   1250   30

ENAME   SAL   DEPTNO
---------- ---------- ----------
JAMES   950   30
BLAKE  2850   30
ALLEN  1600   30

已选择14行。
 
(ename中的第二个字母不能是A,并且工资要大于800,按照工资降序排列)
SQL> select ename,sal*12 annual_sal from emp
  2  where ename not like '_A%' and sal > 800
  3  order by sal desc;

ENAME  ANNUAL_SAL
---------- ----------
KING  60000
SCOTT   36000
FORD  36000
JONES   35700
BLAKE   34200
CLARK   29400
ALLEN   19200
TURNER  18000
MILLER  15600
ADAMS   13200

已选择10行。

CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,