sql系列（基础篇）-第二章约束和排序数据 - 数据库编程

者时间类型的字段进行排序；注意：字符串排序：按照字符对应的ASCII码的先后进行排序；日期排序：按照日期的先后进行排序，时间越往后越大；例：将emp表中员工按照job升序、ename降序进行排列；（先按Job排序，同样的job按ename降序） SCOTT@ORCL>select * from emp 2 order by job asc,ename desc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7369 SMITH CLERK 7902 17-DEC-80 800 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 7900 JAMES CLERK 7698 03-DEC-81 950 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7839 KING PRESIDENT 17-NOV-81 5000 10 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 14 rows selected. 例：将emp表中员工按照 HIREDATE 升序，sal降序排列（如果HIREDATE相同，按照sal降序）先将HIREDATE显示格式设定 SCOTT@ORCL>alter session set nls_date_format='yyyy-mm-dd'; Session altered. SCOTT@ORCL>select * from emp 2 order by hiredate asc,sal desc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 2975 20 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7839 KING PRESIDENT 1981-11-17 5000 10 7902 FORD ANALYST 7566 1981-12-03 3000 20 7900 JAMES CLERK 7698 1981-12-03 950 30 7934 MILLER CLERK 7782 1982-01-23 1300 10 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7876 ADAMS CLERK 7788 1987-05-23 1100 20 14 rows selected. 6. 使用结果集中的别名进行排序；例：按照emp表中员工对于的年薪（sal*12）进行排序； SCOTT@ORCL>

select empno,ename,hiredate,sal*12 year_sal 2 from emp 3 order by year_sal desc; EMPNO ENAME HIREDATE YEAR_SAL ---------- ---------- ---------- ---------- 7839 KING 1981-11-17 60000 7902 FORD 1981-12-03 36000 7788 SCOTT 1987-04-19 36000 7566 JONES 1981-04-02 35700 7698 BLAKE 1981-05-01 34200 7782 CLARK 1981-06-09 29400 7499 ALLEN 1981-02-20 19200 7844 TURNER 1981-09-08 18000 7934 MILLER 1982-01-23 15600 7521 WARD 1981-02-22 15000 7654 MARTIN 1981-09-28 15000 7876 ADAMS 1987-05-23 13200 7900 JAMES 1981-12-03 11400 7369 SMITH 1980-12-17 9600 14 rows selected. 注意：当然也可以按照字符串、日期对应的别名进行排序； 7. 如果排序的字段中包含null值，结果会怎么样？注意：在字段进行比较大小的时候，null 值比任何值都大；例：emp表中员工的信息按照comm降序排列； SCOTT@ORCL>select * from emp 2 order by comm desc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 800 20 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7902 FORD ANALYST 7566 1981-12-03 3000 20 7900 JAMES CLERK 7698 1981-12-03 950 30 7876 ADAMS CLERK 7788 1987-05-23 1100 20 7566 JONES MANAGER 7839 1981-04-02 2975 20 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7934 MILLER CLERK 7782 1982-01-23 1300 10 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7654 MARTIN SALESMAN 7698 19

sql系列（基础篇）-第二章约束和排序数据(四)