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行。